STRUCTURE QUERY LANGUAGE
Write the outputs of SQL commands given in (h ) with the help of the table shown. EmpNo Name Job Mgr Hiredate Sal Deptno Integer Character Character Float Date Float Integer 1.
To select all the information in formation of employee of Dept number 20 Find all employees who are either Clerks or who are earning between 1000 and 2000. To list names of all employees in ascending order of their salary. To display employee’s name, Salary, Deptno for only managers. To count the number of employees with salary <3000. To insert a new row in the Employee table with the following data: 11, “MILLER”, “Manager”, 7698, {25/02/98}, 4300, 20. To display total salary department wise. To display employees whose name starts with ‘C’ To display employees whose salary between 1000 to 5000 arranged on the basis of hiredate Give the output of the following SQL statements: o Select COUNT(*) from Emp; Select MIN(Sal) from Emp where deptno=20; o o Select SUM(Sal) from Emp where job=’Clerk’; o Select AVG(sal) from Emp; o Select job,max(sal),min(sal),avg(sal) from emp group by job; o Select empno,ename,sal+comm “total Salary” from emp; o Select * from emp where hiredate<’ ‘ 12/12/ Consider a table Department having fields as Deptno Deptname Integer Character Display name of employees along with their department having deptno as 10 o or 20. o Display total salary of each department. o Display total salary of each department whose employee name starts with N or P. o Display total salary of each department whose total salary > 20000.
2. Write the SQL command command for (a) (a) to (f) (f) on the the basis basis of the the table table given given below below (Customer). Customer
No. Fname Lname Phone Number(4) Character(20) Character(20) Number(8) Create the table Enter following data
Address Character(20)
No 1 2 3 4 5 6 7 8
Fname Ajay Deepak Rajesh Priya Ramit Arpit Rahul Kisan
Lname Batra Chopra Mehta Kumar Arya Jain Kundra Kumar
Phone 27111333 25656891 23455511 23456334 24567565 23222334 24567856 27898981
Address Rohini Ashok Vihar Ashok Vihar Rohini Model Town Shailmar Bagh Model Town Rohini
To select all the information in formation of employee of Rohini area. n o. as 27111343 where phone number is Update the database set the phone no. 27111333. D ir with the following fields-> Fname, Phone and Address. To create a view called Dir To display the data for Arpit, Rahul and Kisan. To delete the rows where the address is Rohini. Display record on the basis of Fname Display number of records Create another table Sale No Itemcode Quantity Rate Number(4) Number(4) Number(7) Number(8,2) Add the following data No Itemcode Quantity Rate 1 1001 20 34.56 1 1002 30 67.56 3 1001 20 34.56 4 1009 13 359.23 5 1010 15 699.99 3 1008 16 345.34
1. 2. 3. 4. 5.
Display Fname,Lname, amount paid(Quantity* Rate) from customer and sale tables Display total amount paid by each customer Display itemcode,fname, lname from cutomer and sale table Try this out and write the output Select No,count(*) No,count(*) from custom customer er C, Sale S where where C.No=S.No; C.No=S.No; Select * from from customer customer where where Fname Fname loke ‘A%’ ‘A%’ order order by Address Address Select Fname, Fname, Quantity, Quantity,Rate Rate from from Customer Customer C, Sale S where C.No=S.No C.No=S.No;; Select No, Sum(qu Sum(quantity antity)) from Sale group group by by No; Select No,Lnam No,Lname,su e,sum(quant m(quantity) ity) from from customer customer C and Sale S group group by No having having C.no=S.No a To delete the tables physically.
