sql query asked in interview

An interview in Sql Questioning and database programming

Interviewer   "Hi sahil"

Sahil Replied  "hello mam !"

Interviewer "what query you write to find distinct salary from this employee  table "

Sahil Replied  " The query is , select distinct salary from employee "


Interviewer " Retrieve all employees whose address is in Houston Texas "
Sahil Replied  "  SELECT fname , lname from employee where address like '%Houston T%' "


Interviewer " find all employees who were born during 1950's "
Sahil Replied  "  SELECT fname , lname from employee where bdate  like '1950 6 times _' "


Interviewer " Retrieve all employees in department 5 whose salary is between 30000 and 40000 "
Sahil Replied  "  select * from employee  where salary between  30000 and 40000 and dno = 5 "



Interviewer "  find the sum of salaries of all  employees , the maximum salary ,  the minimum salary , and the average salary "
Sahil Replied  "  select sum(salary) , max(salary) , min(salary) , avg(salary) from employee "


Interviewer " find  the sum of the salaries of all employees of the 'Research' department , as well as the maximum salary , the minimum salary , and the average salary in this department "

Sahil Replied  " select sum(salary) , max(salary) , min(salary) , avg(salary)  from (employee join department on dno = dnumber) where dname = 'Research' "

Interviewer " For each project , retrieve the project number , the project name , and the number of employees  who work on that project in these tables "

Sahil Replied  " select pnumber , pname ,count(*) from project , works_on  where pnumber = pno group by pnumber , pname "


Interviewer " For each project on which more than two employees work  , retrieve the project number , the project name , and the number of employees who work on the project "

Sahil Replied  " select pnumber , pname , count(*) , from project , works_on  where pnumber = pno group by pnumber , pname having count(*) >2 "

Interviewer " Retrieve all name of employee whose department are there in dependent table "

Sahil Replied  " select e dot name , e dot lname from , employee as e  , where exists (select * from dependent where e dot ssn = essn and e dot sex = sex and e dot f name = dependent_name) "

Interviewer " Retrieve the name of employees who have no dependents "

Sahil Replied  " select f name , l name  from employee where not exists (select * from dependent where ssn =essn ) "

Interviewer " Write query to change location to 'Bellaire' of project no 10 of department 5 "

Sahil Replied  " update project,  set plocation  ='Bellaire' ,  where p number = 10 and  d num =5 "

Interviewer " Write query to give all employees in the 'research' department a 10 percent raise in salary "

Sahil Replied  " update employee set salary =  salaries*1.1   where dno in ( select dnumber from department where dname = 'Research') "


Interviewer " Make a list of all project numbers for projects that involve an employee whose last name is 'smith' , either as a worker or as a manager of the department that controls the project"

Sahil Replied  " (SELECT DISTINCT PNUMBER FROM PROJECT, DEPARTMENT, EMPLOYEE  WHERE DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith')
UNION
(SELECT DISTINCT PNUMBER FROM PROJECT, WORKS_ON, EMPLOYEE WHERE PNUMBER=PNO AND ESSN=SSN AND LNAME='Smith'); "



Interviewer " How to   specify the constraint that "the salary of an employee must not be greater than the salary of the manager of the department that the employee works for"

Sahil Replied  " CREATE ASSERTION  SALARY_CONSTRAINT CHECK ( NOT EXISTS (SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D  WHERE  E.SALARY>M.SALARY AND E.DNO=D.DNUMBER AND D.MGRSSN= M.SSN) )"


Interviewer " Explain views in sql with example "

Sahil Replied  "  A views is a virtual table , in contrast to  base tables , whose tuples are actually stored in the database
Example of above view is created from this query ,   create view works_on1 as select fname , lname , pname , hours from employee , project , works_on  where ssn =  essn  and pno = pnumber "


Interviewer " Explain except , union , intercept operator in sql  "

Sahil Replied  " Except operator is used to combine two select statements and return rows from the first select statement that are not returned by  the second select statement "

Sahil Replied  "  UNION  operator is used to combine the results of two or more SELECT statements without returning any duplicate rows."

Sahil Replied  " INTERSECT operator used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement."


Interviewer " Explain the difference between union and union all operator in sql "

Sahil Replied  "  UNION operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.
   UNION ALL operator is used to combine the results of two or more SELECT statements with returning  duplicate rows. "
 
 
 Interviewer "  what are different types of join and explain them."

Sahil Replied  " Different types of join are :
   INNER JOIN: returns rows when there is a match in both tables.
    LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN: returns rows when there is a match in one of the tables.
 SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables."

  Sahil replied to all question very clearly