STRUCTURED QUERY LANGUAGE T
|
1.
Ans.
|
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 employee
WHERE
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 in
department with DeptNo = 30.
|
|
WHERE CLAUSE
HAVING 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.
|
|
EMPLOYEE
WORKS
IN DEPARTMENT
DeptNo
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 : ITEM
Table : 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_ID
ItemName
Manufacturer
Price
PC01
Personal Computer
ABC
35000
LC05
Laptop
ABC
55000
Pc03
Personal Computer
XYZ
32000
Pc06
Personal Computer
COMP
37000
Lc03
Laptop
PQR
57000
|
|
C_ID
CustomerName
City
I_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 : SENDER
TABLE : RECIPIENT
To 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.
|
|
SenderID
SenderName
SenderName
SenderCity
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
|
|
RecID
SenderID
RecName
RecAddress
ReCity
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 MOV
Table : MOV
Find 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 as
QTY * 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.
Title
Type
Rating
Stars
Qty
Price
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
|
|
Ans.
|
CREATE TABLE EMP(EmpNo INTEGER(4), DeptNo INTEGER(2), EmpName CHAR(10), Job
CHAR(10), Manager INTEGER(4), HireDate DATE, Salary DECIMAL(7,2),
Commission DECIMAL(7,2));
|
|
|
CREATE
TABLE PROJECT(ProjId INTEGER(4),
ProjDesign CHAR(20), ProjStartDT
DATE,
ProjEndDT DATE, BudgetAmount INTEGER(7), MaxNoStaff INTEGER(2));
|
|
|
CREATE TABLE DEPT(DeptNo INTEGER(2), DeptName
CHAR(12), Location CHAR(12));
|
|
7.
|
Create a table called SALEGRADE with the
columns specified beow :
|
|
|
LowSal NUMBER(7,2)
|
|
|
HighSal NUMBER(7,2)
|
|
|
Grade NUMBER(2)
|
|
Ans.
|
CREATE TABLE SALEGRADE(LowSal DECIMAL(7,2)
CHECK(LowSal>=1000.00), HighSal
DECIMAL(7,2) CHECK(HighSal<=10000.00), Grade INTEGER);
|
|
8.
|
Write SQL commands for (a) to (f) and
write the outputs for (g) on the basis of tables FURNITURE and ARRIVALS:
TABLE :
FURNITURE
|
|
|
NO
|
ITEMNAME
|
TYPE
|
DATEOFSTOCK
|
PRICE
|
DISCOUNT
|
|
|
1
|
White lotus
|
Double Bed
|
23/02/02
|
30000
|
25
|
|
2
|
Pink feather
|
Baby cot
|
20/01/02
|
7000
|
20
|
|
3
|
Dolphin
|
Baby cot
|
19/02/02
|
9500
|
20
|
|
4
|
Decent
|
Office Table
|
01/01/02
|
25000
|
30
|
|
Table
: EMP
Table
: PROJECT
Table
: DEPT
EmpNo
NUMBER(4)
ProjId
NUMBER(4)
DeptNo
NUMBER(2)
DeptNo
NUMBER(2)
ProjDesig
CHAR(20)
DeptName
CHAR(12)
EmpName
CHAR(10)
ProjStartDT
DATE
Location
CHAR(12)
Job
CHAR(10)
ProjEndDT
DATE
Manager
NUMBER(4)
BudgetAmount
NUMBER(7)
Hiredate
DATE
MaxNoStaff
NUMBER(2)
Salary
NUMBER(7,2)
Commission
NUMBER(7,2)
|
|
|
|
5
|
Comfort zone
|
Double Bed
|
12/01/02
|
25000
|
25
|
|
|
|
6
|
Donald
|
Baby cot
|
24/02/02
|
6500
|
15
|
|
|
7
|
Royal Finish
|
Office Table
|
20/02/02
|
18000
|
30
|
|
|
8
|
Royal tiger
|
Sofa
|
22/02/02
|
31000
|
30
|
|
|
9
|
Econo sitting
|
Sofa
|
13/12/01
|
9500
|
25
|
|
(a)
|
10
|
Eating Paradise
|
Dining Table
|
19/02/02
|
11500
|
25
|
|
TABLE : ARRIVALS
To 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.
|
|
NO
ITEMNAME
TYPE
DATEOFSTOCK
PRICE
DISCOUNT
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 : DOCTOR
TABLE : SALARY
Display 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
|
|
ID
NAME
DEPT
SEX
EXPERIENCE
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
|
|
1D
BASIC
ALLOWANCE
CONSULTATION
101
12000
1000
300
104
23000
2300
500
107
32000
4000
500
114
12000
5200
100
109
42000
1700
200
105
18900
1690
300
130
21700
2600
300
|
11.
(a) Ans.
(b)
(i)
Ans.
(ii)
Ans.
(iii) Ans.
(iv)
Ans.
(v) Ans.
What
are DDL and DML commands?
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 : Workers
Table : DESIG
To 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_ID
FIRSTNAME
LASTNAME
ADDRESS
CITY
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
|
|
W_ID
SALARY
BENEFITS
DESIGNATION
102
75000
15000
Manager
105
85000
25000
Director
144
70000
15000
Manager
210
75000
12500
Manager
255
50000
12000
Clerk
300
45000
10000
Clerk
335
40000
10000
Clerk
400
32000
7500
Salesman
451
28000
7500
Salesman
|
|
Ans.
|
FIRSTNAME SALARY
Sam 75000
|
|
|
Manila 70000
George 75000
|
|
(vi)
|
SELECT COUNT(DISTINCT DESIGNATION) FROM DESIG;
|
|
Ans.
|
COUNT(DISTINCT
DESIGNATION)
4
|
|
(vii)
|
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;
|
|
GCODE
DESCRIPTION
PRICE
FCODE
READYDATE
10023
PENCIL SKIRT
1150
F03
19-DEC-08
10001
FORMAL SHIRT
1250
F01
12-JAN-08
10012
INFORMAL SHIRT
1550
F02
06-JAN-08
10024
BABY TOP
750
F03
07-APR-07
10090
TULIP SKIRT
850
F02
31-MAR-07
10019
EVENING GOWN
850
F03
06-JUN-08
10009
INFORMAL PANT
1500
F02
20-OCT-08
10007
FORMAL PANT
1350
F01
09-MAR-08
10020
FROCK
850
F04
09-SEP-07
10089
SLACKS
750
F03
20-OCT-08
|
|
FCODE
TYPE
F04
POLYSTER
F02
COTTON
F03
SILK
F01
TERELENE
|
|
(v)
|
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 : DRESS
Table : MATERIAL
To 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;
|
|
(i)
|
|
Ans.
|
|
(ii)
|
|
Ans.
|
|
(iii)
|
|
Ans.
|
|
(iv)
|
|
Ans.
|
|
DCODE
DESCRIPTION
PRICE
MCODE
LAUNCHDATE
10001
FORMAL SHIRT
1250
M001
12-JAN-08
10020
FROCK
750
M004
09-SEP-07
10012
ONFORMAL SHIRT
1450
M002
06-JUN-08
10019
EVENING GOWN
850
M003
06-JUN-08
10090
TULIP SKIRT
850
M002
31-MAR-07
10023
PENCIL SKIRT
1250
M003
19-DEC-08
10089
SLACKS
850
M003
20-OCT-08
10007
FORMAL PANT
1450
M001
09-MAR-08
10009
INFORMAL PANT
1400
M002
20-OCT-08
10024
BABY TOP
650
M003
07-APR-07
|
|
MCODE
TYPE
M001
TERELENE
M002
COTTON
M004
POLYESTER
M003
SILK
|
|
(v)
|
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 : STATIONERY
Table : CONSUMER
To 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;
|
|
(i)
|
|
Ans.
|
|
(ii)
|
|
Ans.
|
|
(iii)
|
|
Ans.
|
|
(iv)
|
|
Ans.
|
|
(v)
|
|
S_ID
StationeryName
Company
Price
DP01
Dot Pen
ABC
10
PL02
Pencil
XYZ
6
ER05
Eraser
XYZ
7
PL01
Pencil
CAM
5
GP02
Gel Pen
ABC
15
|
|
C_ID
ConsumerName
Address
S_ID
01
Good Lerner
Delhi
PL01
06
Write Well
Mumbai
GP02
12
Topper
Delhi
DP01
15
Write & Draw
Delhi
PL02
16
Motivation
Banglore
PL01
|
|
Ans.
(vi) Ans.
(vii) Ans.
(viii) Ans.
|
Address Delhi Mumbai Banglore
SELECT
Company, MAX(Price),Min(Price),Count(*) FROM Stationery GROUP BY Company;
Company MAX(Price)
Min(Price) Count(*) ABC 17 12 2
CAM 7 7 1
XYZ 9 8 2
SELECT
Consumer.CnsumerName, stationery.stationeryName, Stationery.Price FROM
Stationery, Consumer WHERE Consumer.S_Id=Stationery.S_Id
ConsumerName StationeryName Price Good Lerner Pencil 7
Write Well Gel Pen 17
Topper Dot Pen 12
Write & Drow Pencil 8
Motivation Pencil 7
SELECT
StationeryName, Price * 3 FROM Stationery
StationeryName Price*3 Dot Pen 36
Pencil 24
Eraser 27
Pencil 21
Gel
Pen 51
|
Share this: Please Visit https://pythonclassroomdiary.wordpress.com and share with your friends
Like this:
Like Loading...
