TABLE: EMPLOYEE



Answer the following with respect to SQL:

|RELATION: EMPLOYEE |

|NO |NAME |SALARY |AREA |AGE |GRADE |DEPT |

|2 |KIRTI ARTS |35000 |SOUTH |38 |A |ELEC |

|3 |KRIPPLE |60000 |NORTH |52 |B |CIVIL |

|4 |ARYAN STALL |38000 |NORTH |29 |B |CIVIL |

|5 |SAMSONS |42000 |EAST |35 |A |COMP |

|6 |BISWAL |29000 |SOUTH |34 |A |MECH |

1. To display the name of all the employees who are in the area South.

SELECT NAME FROM EMPLOYEE WHERE AREA =’SOUTH’;

2. To display name and age of all employees having age > 40.

SELECT NAME,AGE FROM EMPLOYEE WHERE AGE>40;

3. To display DEPT, SALARY department wise

SELECT DEPT,SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT;

4. To display the NAME, SALARY, AREA for all the employees

SELECT NAME,SALARY,AREAL FROM EMPLOYEE;

5. To display all the NAME,SALARY for all the employees whose age is between 30 and 50 and whose AREA is SOUTH

SELECT NAME,SALARY,AREAL FROM EMPLOYEE where age between 30 and 50 and AREA =’SOUTH’;

6. To display the list of all employees whose salary >= 30000 and 80

(iii) To display list of all the shops with sale> 300000 in ascending order of Shop_Name.

(iv) To display a report with Shop_name, Area and Rating for each shop in the table, for only

those shops whose sale is betweenJ50000 and 400000 (including both 350000 and 400000).

(v) Select Min(Cust_percent) from SHOP;

(vi ) Select Sum(Sale) fi'ol11 SHOP where Rating = 'A';

(vii) Select Avg(Sale) from SHOP where City = "Delhi";

(viii) Select Count(Distinct City) from SHOP

RELATION : STUDENT

No. Name Age Dept Dateofadm Fee Gender

1 Pankaj 24 Computer 10-Jan-97 120 M

2 Shalini 21 History 24-Mar-98 200 F

3 Sanjay 22 Hindi 12-Dec-96 300 M

4 Sudha 25 History 01-Jul-99 400 F

5 Rakesh 22 Hindi 05-Sep-97 250 M

6 Shakeel 30 History 27-Jun-98 300 M

7 Surya 34 Computer 25-Feb-97 210 M

8 Shikha 23 Hindi 31-Jul-97 200 F

(a) To show all information about the students of History dept. 1

(b) To list the names of female students who are in Hindi dept. 1

(c) To list names of all students with their date of admission in ascending order. 1

(d) To display student’s name, fee, age for male students only. 1

(e) To count the number of students with Age ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download