What is SQL? What are the various
subdivisions of SQL?
SQL
Stands for Structured Query Language. It includes 3 types of commands:
Data
Definition Language (DDL)
Data
Manipulation Language (DML)
Data Control Language (DCL)
2.Ans.
Give examples of some DDL commands and
some DML commands.
DDL Commands
CREATEALTERDROP DML CommandsINSERT INTODELETEUPDATE
3.Ans.
What is the
difference between column constraint and table constraint? Name some database
integrity constrains.
The difference between
column constraint and table constraint is that column constraint applies only
to individual columns, whereas table constraints apply to groups of one or
more columns.
Following are the few of database integrity
constrains:
Unique constraintPrimary Key
constraintDefault constraintCheck constraint
4.Ans.
1. How
do following constraint work?(i) Unique (ii)
Primary Key (iii) Default (iv)
Check Unique: This constraint ensures that no two rows have
the same value in the specified columns. For eg , CREATE TABLE employee
(ecode integer NOT NULL UNIQUE, ename char(20),Sex char(2) );
Primary Key:
Primary key does not allow NULL value and Duplicate data in the column which
is declared as Primary Key.
For eg , CREATE TABLE employee (ecode integer NOT
NULL PRIMARY KEY, ename char(20),Sexchar(2) );
Default: When a
user does not enter a value for the column, automatically the defined default
value is inserted in field. A column can have only one default value.
For eg , CREATE TABLE employee (ecode integer NOT
NULL PRIMARY KEY, ename char(20), Sexchar(2), Grade char(2) DEFAULT = ‘E1’ );
Check: This constraint limits values that can
inserted into a column of table.
For eg , CREATE TABLE employee (ecode integer NOT
NULL PRIMARY KEY, ename char(20),Sex char(2) , Grade char(2) DEFAULT = ‘E1’,
Gross decimal CHECK (gross > 2000 );
5.Ans.
Compare DISTINCT and ALL keywords when
used with SELECT command.
DISTINCT
keyword is used to restrict the duplicate rows from the results of a SELECT
statement. ALL keyword retains the duplicate rows, by default ALL keyword is
use by SELECT statement.
6.
What is wrong with the following
statement? Write the corrected form of this query : SELECT * FROM employeeWHERE
grade = NULL ;
Ans.
IS
NULL should be used in place of = NULL. Following is the correct statement :
SELECT * FROM employee
WHERE grade IS NULL ;
7.Ans.
What is the difference between where and
having clause ?
8.Ans.
What is difference between working of the following functions?
Count(*),Count (<column-name>), Count (DISTINCT), Count (ALL) Count(*):-
The COUNT(*) function returns the number of records in a table:
SELECT COUNT(*) FROM
student;
Count
(<column-name>):- The COUNT(<column-name>) function returns
the number of values (NULL values will not be counted) of the specified
column:
SELECT COUNT(name) FROM
student;
Count (DISTINCT):-
The COUNT(DISTINCT column_name) function returns the number of distinct
values of the specified column:
SELECT COUNT(DISTINCT
city) FROM student;
Count
(ALL):- to count the number of non-null values in column dept, i.e.
counting repetitions too.
SELECT COUNT(ALL) FROM student;
9.Ans.
1. What
is the difference between SELECT INTO and CREATE VIEW commands?
10.Ans.
What are views? When can a view be
updated?
A view is, in essence, a
virtual table. It does not physically exist. Rather, it is created by a query
joining one or more tables.
In following condition a view can be updated :
If it has only one base table.If
select statement used in view contains group by clause then we cant update view.
11.Ans.
What is the condition of dropping a
table?
There
is a one condition for dropping a table that is a table must be an empty
table which we want to drop.
Note: For the
following questions consider the tables EMP given in book.
12.Ans.
Insert a record with suitable data in the
table EMP, tabing system date as the Hiredate.
INSERT INTO
emp VALUES (1101,’ROBIN’,’CLERK’,7902,curdate(),5000.00,500.00,10);
13.
To create a table DEPTO30 to hold the
employee numbers, names, jobs and salaries of employee indepartment with DeptNo = 30.
WHERE CLAUSEHAVING CLAUSE
Places conditions on individual rows.
Places conditions on groups.
Cannot include aggregate function.
Can include aggregate function.
For eg. SELECT * FROM student
WHERE
Rno >=10;
For
eg. SELECT AVG(marks) FROM student
GROUP BY grade HAVING grade = ‘B1’;
SELECT INTO command
CREATE VIEW command
SELECT INTO creates a new table by
extracting data from other table.
The CREATE VIEW
creates view from a table.
Resides physically in the database.
Not a part of the database’s physical
representation.
Used to create backup copies of tables.
Not used for backup purpose.
For eg. SELECT
Lastname, FirstName INTO Person_Backup
FROM Persons;
For eg. CREATE VIEW v_student AS
SELECT Roll_no, Name, Class FROM student;
Ans.
CREATE TABLE DEPTP30 AS(SELECT EmpNo, EmpName, Job, Sal FROM EMP
WHERE
DeptNo=30);
14.Ans.
Display names all employees whose names
include either of the substring “TH” or “LL”.
SELECT
empname FROM emp WHERE(empname LIKE ‘%TH%’ OR empname LIKE ‘%LL%’);
15.Ans.
Display data for all CLERKS who earn
between 1000 and 2000.
SELECT
* FROM emp WHERE((job LIKE ‘clerk’) AND (sal BETWEEN 1000 AND 2000));
16.Ans.
Display data for all employees sorted by
their department, seniority and salary.
SELECT
* FROM emp ORDER BY deptno, hiredate, sal;
17.Ans.
Write a SQL
statement to list EmpNo, EmpName, DeptNo, for all the employees. This
information is should be sorted on EmpName.
SELECT empno, empname,deptno FROM emp
ORDER BY empname;
18.Ans.
Write SQL statement for : Find all the
employees who have no manager.
SELECT
empname FROM emp WHERE mgr IS NULL;
19.Ans.
Write a SQL statement (s) to list all
employees in the following format:
SELECT ename,’WORKS IN DEPARTMENT’, deptno FROM emp;
20.Ans.
To find all those employees whose job
does not start with ‘M’.
SELECT
empname FROM emp WHERE job NOT LIKE ‘M%’;
21.Ans.
To display all employees who were hired
during 1995.
SELECT
ename FROM emp WHERE YEAR(hiredate) = ‘1995’;
22.Ans.
To display DeptNo, Job, EmpName in
reverse order of salary from the EMP table.
SELECT
deptno,job,empname FROM emp ORDER BY sal DESC;
23.Ans.
List EmpName, Job, Sal for all the
employees who have a manager.
SELECT
empname , job, salary from EMP WHERE mgr IS NOT NULL;
24.Ans.
List the minimum and maximum salary of
each job type.
SELECT
job,MIN(sal),MAX(sal) FROM emp GROUP BY job;
25.Ans.
Show the average salary for all
departments with more than 3 people for job.
SELECT
AVG( sal ) FROM emp GROUP BY deptno HAVING COUNT(job)>3;
26.Ans.
Display only the jobs with maximum salary
greater than or equal to 3000.
SELECT
job FROM emp GROUP BY job HAVING MAX(salary)>=3000;
27.Ans.
Find out number of employee having ‘Manager’
as job.
SELECT
COUNT(empname) FROM emp WHERE job LIKE ‘Manager’;
28.Ans.
Create view Dept20 with EmpName and the
Sal of employees for dept 20.
CREATE
VIEW dept20 as SELECT empname, sal FROM emp WHERE deptno=20;
29.Ans.
Find the average salary and average total remuneration for each job
type remember salesman earn commission.
SELECT AVG( sal ) , AVG( sal + IFNULL(
comm, 0 ) ) FROM emp GROUP BY job;
30.Ans.
What happens if you try to drop a table
on which a view exists?
If we try to drop a table
on which a view exist, then the table is dropped but DBMS invalidates these
dependent views but does not drop them. We cannot use these views unless we
recreate the table or drop and recreate the objects so that they no longer
depend on the table.
31.Ans.
Create a view with one of the columns
Salary * 12. Try updating columns of this view.
CREATE VIEW emp_view (v_empno,v_empname,v_avgsal) AS SELECT empno,
empname, salary*12 FROM emp;
UPDATE emp_view SET empname = ‘MOHAN’ WHERE empno=8698;
32.Ans.
Can you create view of view?
Yes,
We can create view of view.
EMPLOYEEWORKS
IN DEPARTMENTDeptNo
7369-SMITH
WORKS IN DEPARTMENT
20
7300-SUDHIR
WORKS IN DEPARTMENT
20
7345-RAJ
WORKS IN DEPARTMENT
10
7329-SMITHS
WORKS IN DEPARTMENT
30
7234-SANTOSH
WORKS IN DEPARTMENT
30
33.Ans.
Write a suitable SQL statement to display ALL employees working in New York in the following format : EmpName Salary Location
SELECT A.empname, A.salary,B.location FROM emp A INNER JOIN dept B
on A.deptno=B.deptno WHERE location LIKE ‘NewYork’;
34.Ans.
Write a suitable SQL statement to display
employees’ name of all the employees of GRADE 3.
SELECT
empname FROM emp A, salgrade B WHERE grade=3 AND A.empno=B.empno;
35.Ans.
Write a suitable SQL statement to find
out the total number of employees from EMP table.
SELECT
count(empname) from EMP;
TYPE B : SHORT ANSWER QUESTIONS
1.
Consider the following tables STORE and
SUPPLIERS and answer (a) and (b) parts of this question:
(a)(i) Ans.(ii) Ans.(iii) Ans.(iv) Ans.
Write SQL commands for the following
statements:To display
details of all the items in the Store table in ascending order of LastBuy.
SELECT
* FROM STORE ORDER BY LastBuy;
To display
ItemNo and Item name of those items from Store table, whose Rate is more than
15 Rupees.
SELECT
ItemNo, Item FROM STORE WHERE Rate >15;
To display the details of those items whose Supplier code (Scode) is
22 or Quantity in Store (Qty) is more than 110 from the table Store.
SELECT * FROM STORE WHERE Scode = 22 OR Qty >110;
To display
Minimum Rate of items for each Supplier individually as per Scode from the
table Store.
SELECT
Scode, MIN(Rate) FROM STORE GROUP BY Scode;
(b)
Give
the output of the following SQL
queries:SELECT COUNT(DISTINCT Scode) FROM Store;
COUNT(DISTINCT Scode) 3
SELECT
Rate*Qty FROM Store WHERE ItemNo=2004;
RATE*QTY 880
SELECT
Item,Sname FROM Store S, Suppliers P WHERE S.Scode=P.Scode AND ItemNo=2006;
ITEM SNAME
Gel Pen Classic Premium Stationers
SELECT MAX(LastBuy) FROM Store;
MAX (LASTBUY) 24-Feb-10
(i)
Ans.
(ii)
Ans.
(iii)
Ans.
(iv)
Ans.
2.
Consider the following table Item and Customer. Write SQL commands
for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii).Table : ITEMTable : CUSTOMER
(i)
To display the details of those Customer
whose City is Delhi.
SELECT
* FROM CUSTOMER WHERE CITY = ‘DELHI’;
To display the
details of Item whose Price is in the range of 3500 to 55000 (Both values
included).
SELECT * FROM ITEM WHERE PRICE BETWEEN 35000 AND 55000;
To displa the customerName, City from
table Customer, and ItemName and Price from table Item, with their
corresponding matching I_ID.
SELECT CUSTOMERNAME,CITY,ITEMNAME,PRICE FROM CUSTOMER A INNER JOIN ITEM B WHERE A.I_ID=B.I_ID;
To increase the Price of all Items by 1000 in the table Item.
UPDATE
ITEM SET PRICE=PRICE+1000;
SELECT DISTINCT City FROM Customer;
City Delhi Mumbai Banglore
Ans.
(ii)
Ans.
(iii)
Ans.
(iv)
Ans.
(v)
Ans.
i_IDItemNameManufacturerPrice
PC01
Personal Computer
ABC
35000
LC05
Laptop
ABC
55000
Pc03
Personal Computer
XYZ
32000
Pc06
Personal Computer
COMP
37000
Lc03
Laptop
PQR
57000
C_IDCustomerNameCityI_ID
01
N Roy
Delhi
LC03
06
H Singh
Mumbai
PC03
12
R Pandey
Delhi
PC06
15
C Sharma
Delhi
LC03
16
K Agarwal
Banglore
PC01
(vi)
SELECT ItemName, Max(Price), Count(*)
FROM Item GROUP BY ItemName;
Name Max(Price) Count(*)
Laptop 58000 2
Personal 38000 3
Computer
SELECT CustomerName, Manufacturer From
Item, Customer WHERE Item.I_Id=Customer.I_Id;
Cname Manufacturer
N Roy PQR
H Singh XYZ R Pandey COMP C Sharma PQR K Agarwal ABC
SELECT
ItemName, Price * 100 FROM Item WHERE Manufacturer = ‘ABC’;
Name Price*100
Personal Computer 3600000
Laptop 5600000
Ans.
(vii)
Ans.
(viii)
Ans.
3.
Consider the
following tables. Write SQL commands for the statements (i) to (iv) and give
outputs for SQL queries (v) to (viii).TABLE : SENDERTABLE : RECIPIENTTo display the names of all Senders from Mumbai
SELECT
SENDERNAME FROM SENDER WHERE SENDERCITY=’MUMBAI’;
To display the
RecID, SenderName, SenderAddress, RecName, RecAddess for every Recipient
SELECT
RECID, SENDERNAME,SENDERADDRESS,RECNAME,RECADDRESS FROM RECIPIENT A INNER
JOIN SENDER B ON A.SENDERID=B.SENDERID;
To display Recipient detail in asending order of RecName
SELECT
* FROM RECIPIENT ORDER BY RECNAME;
To display
number of Recipients from each city
SELECT RECCITY,COUNT(RECNAME) FROM RECIPIENT GROUP BY RECCITY;
SELECT
DISTINCT Sendercity FROM Sender;
Sendercity New Delhi Mumbai
(i)
Ans.
(ii)
Ans.
(iii)
Ans.
(iv)
Ans.
(v)
Ans.
SenderIDSenderNameSenderNameSenderCity
ND01
R Jain
2,ABC Appts
New Delhi
MU02
H Sinha
12, Newtown
Mumbai
MU15
S Jha
27/A, Park Street
Mumbai
ND50
T Prasad
122-K, SDA
New Delhi
RecIDSenderIDRecNameRecAddressReCity
KO05
ND01
R Bajpayee
5, Central Avenue
Kolkata
ND08
MU02
S Mohan
116, A vihar
New Delhi
MU19
ND01
H singh
2a, Andheri east
Mumbai
MU32
MU15
P K Swamy
B5, c S Terminus
Mumbai
ND48
ND50
S Tirupathi
13, B1 d, Mayur vihar
New Delhi
(vi)
SELECT
A.SenderName, B.RecName FROM Sender A, Recipient B WHERE
A.SenderID=B.SenderID AND B.RecCity=’Mumbai’;
SenderName RecName R Jain H Singh
S Jha P
K Swamy
SELECT RecName, RecAddress FROM Recipient WHERE recCity NOT
IN(‘Mumbai’, ‘Kolkata’);
RecName RecAddress
S Mahajan 116, A Viharl
S Tirupati 13, B1 D, Mayur Vihar
SELECT RecID,
RecName FROM Recipient WHERE SenderID=’MU02’ OR SenderID=’ND50’;
RecID RecName ND08 S Mahajan
ND48 S Tirupati
Ans.
(vii)
Ans.
(viii)
Ans.
4. (a)
What happens if you drop a table on which
a view exists?
If we try to drop a table
on which a view exist, then the DBMS like Oracle invalidates these dependent
views but does not drop them. We cannot use these views unless we recreate
the table or drop and recreate the objects so that they no longer depend on
the table.
Note: Write the SQL commands for (b) to (g) and
write outputs for SQL commands given in (h) on the basis of table MOVTable : MOVFind the total
value of the movie cassettes available in the library.
SELECT
COUNT(TITLE) FROM MOV;
Display a list
of all movies with Price over 20 and sorted by Price.
SELECT
* FROM MOV WHERE PRICE>20 ORDER BY PRICE;
Display all
the movies sorted by Qty in decreasing order.
SELECT
* FROM MOV ORDER BY QTY DESC;
Display a report listing a movie number,
current value and replacement value for each movie in the above table.
Calculate the replacement value for all movies asQTY * Price * 1.15
SELECT NO,PRICE AS ‘CURRENT VALUE’,(QTY*PRICE*1.15) AS ‘REPLACEMENT
VALUE’ FROM MOV;
Count the
number of movies where Rating is not “G”.
SELECT
COUNT(TITLE) FROM MOV WHERE RATING<>’G’;
Ans.
(b)
Ans.
(c)
Ans.
(d)
Ans.
(e)
Ans.
(f)
Ans.
No.TitleTypeRatingStarsQtyPrice
1
Gone with the Wind
Drama
G
Gable
4
39.95
2
Friday the 13th
Horror
R
Jason
2
69.95
3
Top Gun
Drama
PG
Cruise
7
49.95
4
Splash
Comedy
PG13
Hanks
3
29.95
5
Independence Day
Drama
R
Turner
3
19.95
6
Risky Business
Comedy
R
Cruise
2
44.95
7
Cocoon
Scifi
PG
Ameche
2
31.95
8
Crocodile Dundee
Comedy
PG13
Harris
2
69.95
9
101 Dalmatians
Comedy
G
3
59.95
10
Tootsie
Comedy
PG
Hoffman
1
29.95
(g)
Insert a new movie in MOV table. Fill all
the columns with values.
Ans.
INSERT IN TO MOV VALUES(11,’Republic Day’,’Drama’,’R’,’Turner’,3,38.95);
(h)
Give the output of following SQL commands on the basis of table MOV.
(i)Ans.
Select AVG(Price) from MOV where Price
< 30;
AVG(Price) 19.95
(ii) Ans.
Select MAX(Price) from MOV where price
> 30;
MAX(Price) 79.95
(iii)
Select
SUM(Price * QTY) from MOV where QTY < 4;
Ans.
SUM(Price*QTY)
791.75
(iv)
Select COUNT(DISTINCT TYPE) from MOV;
Ans.
COUNT(DISTINCT TYPE)
4
5.Ans.
Write SQL statement to create EMPLOYEE
relation which contains EmpNo, Name, Skill, PayRate.
CREATE TABLE
EMPLOYEE(EmpNo VARCHAR(10), Name CHAR(20), Skill CHAR(20),
PayRate DECIMAL);
6.
Create a table with the under mentioned
structures
TABLE : ARRIVALSTo show all information about the Baby cots from the FURNITURE table.
SELECT * FROM FURNITURE WHERE
TYPE=’Baby cot’;
To list the
ITEMNAME which are priced at more than 15000 from the FURNITURE table.
SELECT ITEMNAME FROM FURNITURE WHERE PRICE>15000;
To list
ITEMNAME and TYPE of those items, in which DATEOFSTOCK is before 22/01/02
from the FURNITURE table in descending order of ITEMNAME.
SELECT ITEMNAME, TYPE FROM FURNITURE WHERE DATEOFSTOCK<{22/01/02}
ORDER BY ITEMNAME DESC;
To display ITEMNAME and DATEOFSTOCK of those items, in which the
DISCOUNT percentage is more than 25 form FURNITURE table.
SELECT
ITEMNAME,DATEOFSTOCK FROM FURNITURE WHERE DISCOUNT>25;
To count the
number of items, whose TYPE is “Sofa” from FURNITURE table.
SELECT COUNT(TYPE) FROM FURNITURE
WHERE TYPE=’SOFA’;
To insert a new row in the ARRIVALS table
with the following data: 14, ‘Velvet touch’, ‘Double bed’, {25/03/03},
25000,30
INSERT INTO ARRIVALS VALUES(14,
‘Velvet touch’, ‘Double bed’, {25/03/03},
25000,30);
Give the
output of following SQL statement:NOTE : Outputs of the below mentioned queries should be based on
original data given in both the tables, i.e., without considering the
insertion done in (f) part of this question :Select COUNT (distinct TYPE) from FURNITURE;
COUNT(distinct TYPE) 5
Select MAX(DISCOUT) form FURNITURE,
ARRIVALS;
MAX(DISCOUNT) 30,25
Select AVG(DISCOUT) form FURNITURE where
TYPE = ‘Baby cot’;
AVG(DISCOUT) 15
Select SUM(PRICE) from FURNITURE where
DATEOFSTOCK<{12/02/02};
SUM(PRICE) 66500
Ans.
(b)
Ans.
(c)
Ans.
(d)
Ans.
(e)
Ans.
(f)
Ans.
(g)
(i)
Ans.
(ii)
Ans.
(iii)
Ans.
(iv)
Ans.
9.
Differentiate between SQL commands DROP
TABLE and DROP VIEW.
NOITEMNAMETYPEDATEOFSTOCKPRICEDISCOUNT
11
Wood
Comfort
Double
Bed
23/03/03
25000
25
12
Old
Fox
Sofa
20/02/03
17000
20
13
Micky
Baby
cot
21/02/03
7500
15
Ans.
DROP TABLE:- DROP TABLE
statement is used to delete the table and all its data from the database
entirely. The syntax for DROP TABLE is DROP TABLE ;
DROP VIEW:- Removes an
existing view from a database. DROP VIEW statement is used to remove a view
or an object view from the database. The syntax for DROP VIEW is DROP VIEW ;
10.(i) Ans.(ii) Ans.(iii) Ans.(iv.) Ans.(v) Ans.(vi) Ans.
Study the following tables DOCTOR and SALARY and write SQL commands
for the questions (i) to (iv) and give outputs for SQL queries (v) to (vi) :TABLE : DOCTORTABLE : SALARYDisplay NAME of all doctors who are in
“MEDICINE” having more than 10 year experience from the table DOCTOR.
SELECT NAME FROM DOCTOR WHERE DEPT=’MEDICINE’ AND EXPERIENCE>10;
Display the average salary of all doctors working in “ENT” department
using the tables DOCTOR and SALARY. Salary=BASIC + ALLOWANCE.
SELECT AVG(BASIC+ALLOWANCE) FROM DOCTOR A, SALARY B WHERE DEPT=’ENT’
AND A.ID=B.ID;
Display the
minimum ALLOWANCE of female doctors.
SELECT MIN(ALLOWANCE) FROM DOCTOR
A AND SALARY B WHERE SEX=’F’ AND
A.ID=B.ID;
Display the
highest consultation fee among all male doctor.
SELECT
MAX(CONST) FROM DOCTOR A, SALARY B WHERE SEX=’M’ AND A.ID=B.ID;
SELECT
count(*) from DOCTOR where SEX=”F”.
count(*) 4
SELECT NAME,
DEPT, BASIC from DOCTOR Salary WHERE DEPT=”ENT” AND DOCTORID=SALARY.ID
NAME DEPT BASIC John ENT 12000
IDNAMEDEPTSEXEXPERIENCE
101
John
ENT
M
12
104
Smith
ORTHOPEDIC
M
5
107
George
CARDIOLOGY
M
10
114
Lara
SKIN
F
3
109
K George
MEDICINE
F
9
105
Johnson
ORTHOPEDIC
M
10
117
Lucy
ENT
F
3
111
Bill
MEDICINE
F
12
130
Morphy
ORTHOPEDIC
M
15
DDL is short form
of Data Definition Language statements are used to build and modify the
structure of database, tables and other objects in the database. When you
execute a DDL statement, it takes effect immediately. Some of the commands
comprising DDL are CREATE TABLE, DROP TABLE and CREATE INDEX.
DML is
abbreviation of Data Manipulation Language. It is used to retrieve, store,
modify, delete, insert and update data in database. Examples: SELECT, UPDATE,
INSERT statements.
Study the following tables FLIGHTS and FARES and write SQL commands for the
questions (i) to (iv) and give outputs for SQL queries (v) to (vi).
Table : FLIGHTS
FL_NO
STARTING
ENDING
NO_FLIGHTS
NO_STOPS
IC301
MUMBAI
DELHI
8
0
IC799
BANGALORE
DELHI
2
1
MC101
INDORE
MUMBAI
3
0
IC302
DELHI
MUMBAI
8
0
AM812
KANPUR
BANGALORE
3
1
IC899
MUMBAI
KOCHI
1
4
AM501
DELHI
TRIVANDRUM
1
5
MU499
MUMBAI
MADRAS
3
3
IC701
DELHI
AHMEDABAD
4
0
Table : FARES
FL_NO
AIRLINES
FARE
TAX%
IC701
Indian Airlines
6500
10
MU499
Sahara
9400
5
AM501
Jet Airways
13450
8
IC899
India Airlines
8300
4
IC302
Indian Airlines
4300
10
IC799
Indian Airlines
10500
10
MC101
Deccan Airlines
3500
4
Display
FL_NO and NO_FLIGHTS from “KANPUR” to “BANGLORE” from the table FLIGHTS.
SELECT FL_NO,NO_FLIGHTS FROM FLIGHTS WHERE
‘STARTING’ LIKE ‘KANPUR’ AND
ENDING LIKE
‘BANGALORE’;
Arrange the contents of the table FLIGHTS in the ascending order of FL_NO.
SELECT * FROM
FLIGHTS ORDER BY FL_NO
Display the FL_NO and fare to be
paid for the flights from DELHI to MUMBAI using the tables FLIGHTS and FARES,
where the fare to be paid=FARE + FARE*TAX%/100.
SELECT `FLIGHTS`.`FL_NO`,(`FARE`+`FARE`*(`TAX%`/100)) FROM `FLIGHTS`,`FARES`
WHERE `STARTING` LIKE ‘DELHI’ AND `ENDING` LIKE ‘MUMBAI’ AND
`FLIGHTS`.`FL_NO` = `FARES`.`FL_NO`;
Display the minimum fare “Indian Airlines” is offering from the table
FARES.
SELECT MIN(FARE)
FROM FARES;
SELECT FL_NO, NO_FLIGHTS, AIRLINES from FLIGHTS,
FARES WHERE STARTING = “DELHI” AND FLIGHTS.FL_NO=FARES.FL_NO.
ERROR – Column ‘FL_NO’ in field list is ambiguous
Correct Code and
Ans.
(vi) Ans.
SELECT
FLIGHTS.FL_NO,NO_FLIGHTS,FARES.AIRLINES FROM FLIGHTS,FARES WHERE FLIGHTS.STARTING
LIKE ‘DELHI’ AND FLIGHTS.FL_NO = FARES.FL_NO; FL_NO NO_FLIGHTS AIRLINES
IC302 8 Indian Airlines
AM501 1 Jet Airways
IC701 4 Indian Airlines
SELECT count(distinct ENDING) from FLIGHTS.
count(distinct ENDING) 7
12.(i) Ans.(ii) Ans.(iii) Ans.(iv) Ans.(v)
Consider the
following tables WORKERS and DESIG. Write SQL commands for the statements (i)
to (iv) and give outputs for SQL queries (v) to (viii).Table : WorkersTable : DESIGTo display W_ID Firstname, Address and City of all employees living
in New York from the table WORKERS.
SELECT
W_ID,FIRSTNAME,ADDRESS,CITY FROM WORKERS WHERE CITY=’NEW YORK’;
To dislay the
content of WORKERS table in ascending order of LASTNAME.
SELECT
* FROM WORKERS ORDER BY LASTNAME;
To display the
Firstname, Lastname, and Total Salary of all clerk from the tables WORKERS
and DESIG, where Total Salary is calculated as Salary + Benefits.
SELECT FIRSTNAME,LASTNAME,SALARY+BENEFITS AS ‘TOTAL SALARY’ FROM
WORKERS,DESIG WHERE WORKERS.W_ID=DESIG.W_ID;
To display the
Minimum salary among Managers and Clerks from the table DESIG.
SELECT
MIN(SALARY) FROM DESIG WHERE DESIGNATION IN(‘MANAGER’,’CLERK’);
SELECT
FIRSTNAME, SALARY FROM WORKERS, DESIG WHERE DESIGNATION = ‘Manager’ AND
WORKERS.W_ID=DESIG.W_ID;
W_IDFIRSTNAMELASTNAMEADDRESSCITY
102
Sam
Tones
33
Elm St.
Paris
105
Sarah
Ackerman
440
U. S. 110
New
York
144
Manila
Sengupta
24
Friends Street
New
Delhi
210
George
Smith
83
First Street
Howard
255
Mary
Jones
842
Vine Ave.
Losantiville
300
Robert
Samuel
9
Fifth Cross
Washington
335
Henry
Williams
12
Moore Street
Boston
403
Ronny
Lee
121
Harrison St.
New
York
451
Pat
Thompson
11
Red Road
Paris
SELECT DESIGNATION, SUM(SALARY) FROM
DESIG GROUP BY DESIGNATION HAVING COUNT(*)<3;
Ans.
DESIGNATION SUM(SALARY)
Director 85000
Salesman 60000
(viii)
SELECT SUM(BENEFITS) FROM DESIG WHERE
DESIGNATION=’Salesman’;
Ans.
SUM(BENEFITS)
15000
13.
Consider the following tables GARMENT and
FABRIC. Write SQL commands for the statements (i) to (iv) and
give outputs for SQL queries (v) to
(viii).
Table: GARMENT
Table: FABRIC
(i) Ans.
To display GCODE and DESCRIPTION of each GARMENT in descending order
of GCODE
SELECT
GCODE, DESCRIPTION FROM GARMENT ORDER BY GCODE DESC;
(ii)
To display the details of all the GARMENTs, which have READYDATE in
between 08-DEC-07 and 16-JUN-08
Ans.
(inclusive of both the dates).
SELECT * FROM GARMENTWHERE
READYDATE BETWEEN ‘08-DEC-07’AND ‘16-JUN-08’;
(iii) Ans.
To display the average PRICE of all the GARMENTs, which are made up
of FABRIC with FCODE as F03.
SELECT AVG(PRICE) FROM
GARMENTWHERE FCODE = ‘F03’;
(iv) Ans.
To display FABRICwise highest and lowest price of GARMENTs from
GARMENT table. (Display FCODE of each GARMENT along with highest and lowest
price).
SELECT
FCODE, MAX(PRICE), MIN(PRICE) FROM GARMENT GROUP BY FCODE;
SELECT SUM(PRICE) FROM GARMENT WHERE
FCODE=’F01’;
SUM(PRICE) 2600
SELECT
DESCRIPTION, TYPE FROM GARMENT, FABRIC WHERE GARMENT.FCODE =FABRIC.FCODE AND
GARMENT.PRICE > = 1260;
DESCRIPTION TYPE INFORMAL SHIRT
COTTON INFORMAL PANT COTTON FORMAL PANT TERELENE
SELECT
MAX(FCODE) FROM FABRIC;
MAX(FCODE) F04
SELECT COUNT
(DISTINCT PRICE) FROM GARMENT;
COUNT(DISTINCT PRICE) 7
Ans.
(vi)
Ans.
(vii)
Ans.
(viii)
Ans.
14.
Consider the following tables DRESS and MATERIAL. Write SQL commands
for the statements (i) to (iv) and give outputs for SQL queries (v) to
(viii).Table : DRESSTable : MATERIALTo display DCODE and DISCRIPTION of each dress in ascending order of DCODE.
SELECT
DCODE,DESCRIPTION FROM DRESS ORDER BY DCODE;
To display the details of all the dresses
which have LAUNCHDATE in between 05-DEC-07 AND 20-JUN-08 (inclusive of both
the dates).
SELECT
* FROM DRESS WHERE LAUNCHDATE BETWEEN ’05-DEC-07′ AND ’20-JUN-08′;
To display the
average PRICE of all the dresses which are made up of material with MCODE as
M003.
SELECT
AVG(PRICE) FROM DRESS WHERE MCODE=’M003′;
To display materialwie highest and lowest price of dresses from DRESS
table. (Display MCODE of each dress along with highest and lowest price)
SELECT B.MCODE,TYPE,MAX(PRICE) AS “HIGHEST”,MIN(PRICE) AS
“LOWEST” FROM DRESS A, MATERIAL B WHERE A.MCODE=B.MCODE GROUP BY
TYPE;
SELECT SUM(PRICE) FROM DRESS WHERE MCODE
= ‘M001’;
SUM(PRICE) 2700
SELECT
DESCRIPTION, TYPE FROM DRESS, MATERIAL WHERE DRESS.MCODE=MATERIAL.MCODE AND
DRESS.PRICE >= 1250;
DESCRIPTION TYPE FORMAL SHIRT TERELENE INFORMAL SHIRT COTTON PENCIL SKIRT SILK
FORMAL PANT TERELENE INFORMAL PANT COTTON
SELECT MAX(MCODE) FROM MATERIAL;
MAX(MCODE) M004
SELECT COUNT(DISTINCT PRICE) FROM DRESS;
COUNT(DISTINCT PRICE) 6
Ans.
(vi)
Ans.
(vii)
Ans.
(viii)
Ans.
15.
Consider the
following tables Stationery and Consumer. Write SQL commands for the
statement (i) to (iv) and give output for SQL queries (v) to (viii).Table : STATIONERYTable : CONSUMERTo display the details of those Consumers whose Address is Delhi.
SELECT * FROM CONSUMER WHERE ADDRESS=”DELHI”;
To display the details of Stationery whose Price is in the range of 8
to 15 (Both value included)
SELECT
* FROM STATIONERY WHERE PRICE BETWEEN 8 AND 15;
To display the
ConsumerName, Address from Tble Consumer, and Company and Price from table
Stationery, with their corresponding matching S_ID
SELECT CONSUMERNAME,ADDRESS,COMPANY,PRICE FROM CONSUMER,STATIONERY
WHERE CONSUMER.S_ID=STATIONERY.S_ID;
To increase
the Price of all stationery by 2
UPDATE
STATIONERY SET PRICE=PRICE+2;
SELECT DISTINCT
Address FROM Consumer;
