Interview questions on SQL

Here are 5 interview questions on SQL, please try and share the answer in comments section

1. STudentDetails: StudentID, courseID, InstructorID, EnrollmentDate
Query to get the list of students who have enrolled to ALL COURSES available in 2016.


select StudentID from STudentDetails where EnrollmentDate>='2016-01-01 00:00:00' and EnrollmentDate<'2017-01-01 00:00:00'
and courseID

select distinct courseID from STudentDetails

2. Avg no. of students per course in 2016.
Eg : If 2 Courses and 10 students in C1 and 20 students in C2, then I would like to get avg number as 15.

select avg(count(StudentID))
from STudentDetails
where courseID in ('C1','C2')
group by courseID

3. Product : ID, Name, Unitcost
SalesRep: ID, Name
SAles : ProductID, salesrepId, custID, qty, sales_date

Query to get the list of productid, salesrepId where the Salesrep has sold a particular product in consecutive days.

S1 P1 01 Jan 2016
S1 P1 02 Jan 2016
S1 P2 03 Jan 2016
S1 p1 04 Jan 2016
S1 P1 05 jan 2016

Resuit set :
S1 | P1 01 Jan 2016 02 jan 2016
S1 | P1 04 Jan 2016 05 Jan 2016


select salesrepId,ProductID
    case(when sales_date<) as start_date,
    case(when ) as end_date
from SAles
group by salesrepId,ProductID,start_date,end_date

4. Design employee dimension as per SCD Type 2.

Employee :
    ID (auto-increment)
    EmpId
    Name
    City
    HQ,
    DeptID
    MangerID
    Bank Account
    StartDate

Dept:
    DeptID
    Name
   
ID,EmpId, Name, City, Startdate
1, 1, Mr. A, Hyd, 2016-01-01
2, 1, Mr. A, Pune, 2016-07-01

5. What is a role playing dimension?