Computer Science
With reference to the table below, answer the questions that follow :
Table : Employees
| Empid | Firstname | Lastname | Address | City |
|---|---|---|---|---|
| 010 | Ravi | Kumar | Raj nagar | GZB |
| 105 | Harry | Waltor | Gandhi nagar | GZB |
| 152 | Sam | Tones | 33 Elm St. | Paris |
| 215 | Sarah | Ackerman | 440 U.S. 110 | Upton |
| 244 | Manila | Sengupta | 24 Friends street | New Delhi |
| 300 | Robert | Samuel | 9 Fifth Cross | Washington |
| 335 | Ritu | Tondon | Shastri Nagar | GZB |
| 400 | Rachel | Lee | 121 Harrison St. | New York |
| 441 | Peter | Thompson | 11 Red Road | Paris |
Table : EmpSalary
| Empid | Salary | Benefits | Designation |
|---|---|---|---|
| 010 | 75000 | 15000 | Manager |
| 105 | 65000 | 15000 | Manager |
| 152 | 80000 | 25000 | Director |
| 215 | 75000 | 12500 | Manager |
| 244 | 50000 | 12000 | Clerk |
| 300 | 45000 | 10000 | Clerk |
| 335 | 40000 | 10000 | Clerk |
| 400 | 32000 | 7500 | Salesman |
| 441 | 28000 | 7500 | Salesman |
Give the Output of following SQL commands :
(i) Select firstname, Salary from Employees, Empsalary where Designation = 'Salesman' and Employees.Empid = Empsalary.Empid ;
(ii) Select count(distinct designation) from EmpSalary ;
(iii) Select designation, sum(salary) from EmpSalary group by designation having count(*) > 2 ;
(iv) Select sum(Benefits) from EmpSalary where Designation = 'Clerk' ;
Answer
(i)
Output
+-----------+--------+
| FIRSTNAME | SALARY |
+-----------+--------+
| RACHEL | 32000 |
| PETER | 28000 |
+-----------+--------+
(ii)
Output
+-----------------------------+
| COUNT(DISTINCT DESIGNATION) |
+-----------------------------+
| 4 |
+-----------------------------+
(iii)
Output
+-------------+-------------+
| DESIGNATION | SUM(SALARY) |
+-------------+-------------+
| MANAGER | 215000 |
| CLERK | 135000 |
+-------------+-------------+
(iv)
Output
+---------------+
| SUM(BENEFITS) |
+---------------+
| 32000 |
+---------------+
Related Questions
With reference to following relations PERSONAL and JOB answer the questions that follow :
Create following tables such that Empno and Sno are not null and unique, date of birth is after '12-Jan-1960', name is never blank, Area and Native place is valid, hobby, dept is not empty, salary is between 4000 and 10000.
Table : Personal
Empno Name Dobirth Native-place Hobby 123 Amit 23-Jan-1965 Delhi Music 127 Manoj 12-dec-1976 Mumbai Writing 124 Abhai 11-aug-1975 Allahabad Music 125 Vinod 04-apr-1977 Delhi Sports 128 Abhay 10-mar-1974 Mumbai Gardening 129 Ramesh 28-oct-1981 Pune Sports Table : Job
Sno Area App_date Salary Retd_date Dept 123 Agra 25-jan-2006 5000 25-jan-2026 Marketing 127 Mathura 22-dec-2006 6000 22-dec-2026 Finance 124 Agra 19-aug-2007 5500 19-aug-2027 Marketing 125 Delhi 14-apr-2004 8500 14-apr-2018 Sales 128 Pune 13-mar-2008 7500 13-mar-2028 Sales 129 Bangalore 21-july-2003 7000 21-july-2023 Finance (a) Show empno, name and salary of those who have Sports as hobby.
(b) Show name of the eldest employee.
(c) Show number of employee area wise.
(d) Show youngest employees from each Native place.
(e) Show Sno, Name, Hobby and Salary in descending order of Salary.
(f) Show the hobbies of those whose name pronounces as 'Abhay'.
(g) Show the appointment date and native place of those whose name starts with 'A' or ends in 'd'.
(h) Show the salary expense with suitable column heading of those who shall retire after 20-jan-2006.
(i) Show additional burden on the company in case salary of employees having hobby as sports, is increased by 10%.
(j) Show the hobby of which there are 2 or more employees.
(k) Show how many employee shall retire today if maximum length of service is 20 years.
(l) Show those employee name and date of birth who have served more than 17 years as on date.
(m) Show names of those who earn more than all of the employees of Sales dept.
(n) Increase salary of the employees by 5 % of their present salary with hobby as Music or they have completed atleast 3 years of service.
(o) Write the output of :
- Select distinct hobby from personal ;
- Select avg(salary) from personal, job where Personal.Empno = Job.Sno and Area in ('Agra','Delhi') ;
- Select count(distinct Native_place) from personal.
- Select name, max(salary) from Personal, Job where Personal.Empno = Job.Sno;
(p) Add a new tuple in the table Personal essentially with hobby as Music.
(q) Insert a new column email in Job table
(r) Create a table with values of columns empno, name, and hobby.
(s) Create a view of Personal and Job details of those who have served less than 15 years.
(t) Erase the records of employee from Job table whose hobby is not Sports.
(u) Remove the table Personal.
With reference to the table below, answer the questions that follow :
Table : Employees
Empid Firstname Lastname Address City 010 Ravi Kumar Raj nagar GZB 105 Harry Waltor Gandhi nagar GZB 152 Sam Tones 33 Elm St. Paris 215 Sarah Ackerman 440 U.S. 110 Upton 244 Manila Sengupta 24 Friends street New Delhi 300 Robert Samuel 9 Fifth Cross Washington 335 Ritu Tondon Shastri Nagar GZB 400 Rachel Lee 121 Harrison St. New York 441 Peter Thompson 11 Red Road Paris Table : EmpSalary
Empid Salary Benefits Designation 010 75000 15000 Manager 105 65000 15000 Manager 152 80000 25000 Director 215 75000 12500 Manager 244 50000 12000 Clerk 300 45000 10000 Clerk 335 40000 10000 Clerk 400 32000 7500 Salesman 441 28000 7500 Salesman Write the SQL commands for the following using above tables :
(i) To show firstname, lastname, address and city of all employees living in Pairs.
(ii) To display the content of Employees table in descending order of Firstname.
(iii) To display the firstname, lastname and total salary of all managers from the tables Employes and EmpSalary, where total salary is calculated as Salary + Benefits.
(iv) To display the maximum salary among managers and clerks from the table EmpSalary.
Show the average salary for all departments with more than 3 people for a job.
Table: Empl
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 8369 SMITH CLERK 8902 1990-12-18 800 NULL 20 8499 ANYA SALESMAN 8698 1991-02-20 1600 300 30 8521 SETH SALESMAN 8698 1991-02-22 1250 500 30 8566 MAHADEVAN MANAGER 8839 1991-04-02 2985 NULL 20 8654 MOMIN SALESMAN 8698 1991-09-28 1250 1400 30 8698 BINA MANAGER 8839 1991-05-01 2850 NULL 30 8839 AMIR PRESIDENT NULL 1991-11-18 5000 NULL 10 8844 KULDEEP SALESMAN 8698 1991-09-08 1500 0 30 8882 SHIAVNSH MANAGER 8839 1991-06-09 2450 NULL 10 8886 ANOOP CLERK 8888 1993-01-12 1100 NULL 20 8888 SCOTT ANALYST 8566 1992-12-09 3000 NULL 20 8900 JATIN CLERK 8698 1991-12-03 950 NULL 30 8902 FAKIR ANALYST 8566 1991-12-03 3000 NULL 20 8934 MITA CLERK 8882 1992-01-23 1300 NULL 10 Display only the jobs with maximum salary greater than or equal to 3000.
Table: Empl
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 8369 SMITH CLERK 8902 1990-12-18 800 NULL 20 8499 ANYA SALESMAN 8698 1991-02-20 1600 300 30 8521 SETH SALESMAN 8698 1991-02-22 1250 500 30 8566 MAHADEVAN MANAGER 8839 1991-04-02 2985 NULL 20 8654 MOMIN SALESMAN 8698 1991-09-28 1250 1400 30 8698 BINA MANAGER 8839 1991-05-01 2850 NULL 30 8839 AMIR PRESIDENT NULL 1991-11-18 5000 NULL 10 8844 KULDEEP SALESMAN 8698 1991-09-08 1500 0 30 8882 SHIAVNSH MANAGER 8839 1991-06-09 2450 NULL 10 8886 ANOOP CLERK 8888 1993-01-12 1100 NULL 20 8888 SCOTT ANALYST 8566 1992-12-09 3000 NULL 20 8900 JATIN CLERK 8698 1991-12-03 950 NULL 30 8902 FAKIR ANALYST 8566 1991-12-03 3000 NULL 20 8934 MITA CLERK 8882 1992-01-23 1300 NULL 10