MySQL

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
Advertisement