Monday, 2 November 2020

SQL Query's

 

SQL Query Interview Questions with Answers

SQL COALESCE Function
The SQL Coalesce function is used to return the first, not Null value from the series of expressions. Let us see how to use SQL Server Coalesce with an example.


SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL);

Error in SQL:

Error: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

Inner Join: It is used to retrieve matching records from both the tables

Department:

Department_NoDepartment_Name
10ECE
20ECE
30CSE
40IT

Employee Details:

Employee_NoEmp_NameAddressAgeDepartment_NoSalary
1AnilHyderabad231020000
2SunilHyderabad221021000
3AjayChennai242023000
4VijayChennai253022000
5JamesHyderabad2450230000

Q1) Write a Query to display employee details who are working in the ECE department?

Ans. 

1
2
3
4
SELECT employee.employee_name, employee.address, employee.salary, employee.age,
FROM Department D
INNER JOIN Employees E
ON department.D_no=employee.D_no WHERE department.D_name= ‘ECE’

Q2) Write a Query to display employee details?

Ans.

1
SELECT * FROM employee;

Q3)  Write a Query to display employee details along with department_name?

Ans.

1
2
3
4
SELECT employee.employee_no, employee.employee_name, employee.address, employee.salary, employee.age, department.department_name
FROM department D
INNER JOIN employee E
ON department.D_no=employee.D_no

Q4) Write a Query to display employee details whose sal>20000 and who is working in the ECE department?

Ans.

1
2
3
4
5
SELECT employee.employee_no, employee.employee_name, employee.address, employee.salary, employee.age
FROM department D
INNER JOIN employee E 
ON dept.D_no=emp.D_no
WHERE dept.D_name=’ECE’ and E.salary>20000

5) Write a Query to display employee details along with department_name and who is working in the ECE department, whose name starts with a?

Ans.

1
2
3
4
5
SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.dname
FROM department D
INNER JOIN employee E
ON dept.D_no=emp.D_no
WHERE dept.D_name=’ECE’ and emp.E_name like ‘a%’

Q6) Write a Query to display employee details along with department_name and whose age between 20 and 24?

Ans.

1
2
3
4
5
SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name
FROM department D
INNER JOIN employee E
ON dept.D_no=emp.D_no
WHERE E.age between 20 and 24

Q7) Write a Query to display employee details along with department_name and who are staying in Hyderabad?

Ans.

1
2
3
4
5
SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name
FROM department D
INNER JOIN employee E
ON dept.D_no=emp.D_no
WHERE E.address=’hyd’

Q8)  Write a Query to display employee details whose salary>20000 and whose age>20 & who is working in the ECE department?

Ans.

1
2
3
4
5
SELECT emp.e_no, emp.e_name, emp.address, emp.salary, emp.age, dept.d_name
FROM department D
INNER JOIN employee E
ON dept.D_no=emp.D_no
WHERE E.age>20 and E.salary>20000 and dept.D_name=’ECE’

State Table:

State IDState Name
S1Telangana
S2AP
S3Tamil Nadu
S4Karnataka
S5Kerala

City

City IDCity NameState ID
1HyderabadS1
2VizagS2
3VijayawadaS2
4ChennaiS3
5MadhuraiS3
6BangaloreS4

Blood Group Details

Blood Group IDBlood Group
B1A+ve
B2B+ve
B3AB +ve
B4A -ve
B5O +ve

Donor Details

Donor IDDonor NamePhone Number City IDBlood Group ID
D1Anil99991B1
D2Sunil88881B1
D3Ajay77772B1
D4John66664B3
D5James55554B5

Q9) Write a Query to display city names belongs to AP?

Ans.

1
2
3
4
5
SELECT C.City_Name
FROM State S
INNER JOIN City C
ON S.State_ID
WHERE S.State_Name ‘AP’

Q10) Write a Query to display Donor_ID, Donor_Name, Phone No, City?

Ans.

1
2
3
4
SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name
FROM Donor D
INNER JOIN City C
ON D.City_ID=C.City_ID

Q11) Write a Query to display Donor_ID, Donor_Name, Phone No, Blood Group?

Ans.

1
2
3
4
SELECT D.Donor_ID, D_Name, D_Phone No, B.Blood_Group
FROM Donor D
INNER JOIN Blood B
ON D.Blood_ID=B.Blood_ID;

Q12) Write a Query to display Donor_ID, Donor_Name, Phone No and who are staying in Hyderabad?

Ans.

1
2
3
4
5
SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name
FROM Donor D
INNER JOIN City C
ON C.City_ID=D.City_ID
WHERE C.City_Name=’hyderabad’

Q13) Write a Query to display donor details whose blood group is A +ve?

Ans.

1
2
3
4
5
SELECT D.Donor_ID, D_Name, D_Phone No
FROM Donor D
INNER JOIN Blood B
ON D.Donor_ID=B.Blood_ID
WHERE B.Blood_Group=’A+ve’

Q14) Write a Query to display Donor_ID, Donor_Name, Phone No, City, Blood Group?

Ans.

1
2
3
4
5
6
SELECT D.Donor_ID, D_Name, D_Phone No, C.City_Name B.Blood_Group
FROM Blood B
INNER JOIN Donor D
ON D.Blood_ID=B.Donor_Name
INNER JOIN City C
ON D.City_ID=C.City_ID

Q15) Write a Query to display Donor_Name, Phone No, Blood Group of the donors who are staying in Hyderabad and whose blood group is A+ve?

Ans.

1
2
3
4
5
6
7
SELECT D.Donor_Name, D. Phone_Number, B.Blood_Group
FROM Donor D
INNER JOIN Blood B
ON D.Blood_ID=B.Blood_ID
INNER JOIN City C
ON D.City_ID=C.City_ID
WHERE C.City_Name=’hyderabad’ and B.Blood_Group=’A+ve’

Outer Join A join that includes rows even if they do not have related rows in the joined table is an Outer Join.. You can create three different outer join to specify the unmatched rows to be included:

  • Left Outer Join
  • Right Outer Join
  • Full Outer Join

Employee Details Table

Employee_NoEmployee_NameDept_No
101Anil10
102Sunil20
103Ajay30
104Vijay40
105NullNull

Department Details Table

Dept_NoDepat_Name
10EEE
20EEE
30CSE
NullNull
50IT

Q16) Write a Query to display only left records?

Ans.

1
2
3
4
5
SELECT e.*
FROM Employee E
LEFT OUTER JOIN Department D
ON E.D_no
WHERE D.D_No IS NULL

Q17) Write a Query to display employee details where employee no is 101?

Ans.

1
2
3
SELECT *
FROM Employee E
WHERE E_No=101

Q18) Write a Query to display employee details where employee number is null?

Ans.

1
2
3
SELECT *
FROM Employee E
WHERE E_No IS NULL

Q19) Write a Query to display only right records?

Ans.

1
2
3
4
5
SELECT D.*
FROM Employee E
RIGHT OUTER JOIN Department D
ON E.D.No=D.D_No
WHERE E.D_No IS NULL

Q20) Write a Query to display all the records from the table except matching records?

Ans.

1
2
3
4
5
SELECT E.*, D.*
FROM Employee E
FULL JOIN Department D
ON E.D_No=D.D_No
WHERE E.D_No IS NULL or D.D_No IS NULL

Department Details Table

Dept_NoDept_Name
1ECE
2CSE
3EEE

Course Details Table

Course_IDCourse_NameCr
1EDC4
2PDC4
3SS4
4DAA4
5OS4

Student Details Table

Student_NoStudent_Name
101Anil
102Sunil
103Ajay
104Vijay
105John

Enroll Details Table

Enroll_DateStudent_NoDpet_NoS_ID
1/2/201410110S1
3/2/201610210S1
3/2/201610310`S1
3/2/201610420S2
3/2/201610520S2

Address Table

Emp_NoAddress
E1Hyderabad
E2Vizag
E3Hyderabad
E4Bangalore
E5Hyderabad

Employee Details Table

Emp_NoEmp_Name
E1Arun
E2Kiran
E3Kumar
E4Anus
E5James

Semester Details Table

SemesterSn
S11
S22-1
S32-2
S43-1
S53-2
S64-1
S74-2

Course Department Details

Dept_NoCourse_ID
101
102
103
204
205

Syllabus Table

Dept_NoCourse_IDS_ID
101S1
102S1
103S1
204S2
205S2

Instructor Details Table

Emp_NoDept_No
E110
E210
E310
E420
E530

Course Instructor Table

Course_IDEmp_NoS_IDDept_No
1E1S110
1E1S120
1E2S130
2E3S110
4E4S220
5E4S220
5E5S110

Q21) Write a query to display Student No, Student Name, Enroll Date, Department Name?

Ans.

1
2
3
4
5
6
SELECT S.Student_No,   S.Student_Name, S.Enroll_Date, D.Dept_Name
FROM Student S
INNER JOIN Enroll E
ON S.Student_No=E.Student_No
INNER JOIN Department D
ON D.Dept_No=E.Dept_No
Q22) Write a query to display Employee Number, Employee Name and address, department name?

Ans.

1
2
3
4
5
6
7
8
SELECT E.Emp_No, E.Emp_Name, A.Address, D.Dept_Name
FROM Employee E
INNER JOIN Address A
ON E.Emp_No=A.Emp_No
INNER JOIN Instructor I
ON A.Emp_No=I.Emp_No
INNER JOIN Department D
ON I.Dept_No=D.Dept_No
Q23) Write a query to display course name belongs to ECE department?

Ans.

1
2
3
4
5
6
7
SELECT C.Course_Name
FROM Department D
INNER JOIN Course Department CD
ON D.Dept_NO=CD.Dept_NO
INNER JOIN Course C
ON CD.CourseDept_ID=C.Course_ID
WHERE D.Dept_Name=’ECE’
Q24) Write a query to display student number, student name, enroll date, dept name,  semester name?

Ans.

1
2
3
4
5
6
7
8
SELECT S.Student_No, S.Student_Name, S.Enroll_Date, D.Dpet_Name, Sem.Student_Name
FROM Enroll E
INNER JOIN Student S
ON S.Student_No=E.Student_No
INNER JOIN Deprtment D
ON E.Dept_No=D.Dept_No
INNER JOIN Semester SE
ON E.Student_ID=Sem.Student_ID

Q25) Write a query to display the syllabus of ECE department 1st year?

Ans.

1
2
3
4
5
6
7
8
9
SELECT C.Course_Name
FROM Department D
INNER JOIN Syllabus Sy
ON D.Dept_No=Sy.Dept_No
INNER JOIN Course C
ON Sy.Course_ID=C.Course_ID
INNER JOIN Semester Se
ON Syllabus_Sy_ID=Se_Sy_ID
WHERE D.Dept_Name=’ECE’ and Se.Semester=’1’
Q26) Write a query to display the employee names and faculty names of ECE dept 1st year?

Ans.

1
2
3
4
5
6
7
8
9
SELECT E.Emp_Name
FROM Employee E
INNER JOIN Course Instructor Ci
ON E.Emp_No=Ci.Emp_No
INNER JOIN Semester Se
ON Se.Student_ID=Ci.Student_ID
INNER JOIN Dept D
ON Ci.Dept_No=D.Dept_No
WHERE D.Dept_Name=’ECE’ and Se.Student_Name=’1’
Q27) Write a query to display student details who enrolled for the ECE department?

Ans.

1
2
3
4
5
6
7
SELECT S.Student_NO, S.Student_Name, S.Enroll_Date
FROM Student S
INNER JOIN Enroll E
ON S.Student_No=E.Student_No
INNER JOIN Department D
ON E.Dept_No=D.Dept_No
WHERE D.Dept_Name=’ECE’
Q28) Write a query to display student details along with dept name who are enrolled in the ECE department the first year?
Ans.
1
2
3
4
5
6
7
8
9
SELECT S.Student_No, S.Student_Name, S.Enroll_Date, D.Dept_Name
FROM Student S
INNER JOIN Enrollment E
ON S.Student_No=E.Student_No
INNER JOIN Department D
ON D.Dept_No=E.Dept_No
INNER JOIN Semester Se
ON E.Student_ID=Se.Student_ID
WHERE D.Dept_Name=’ECE’ and Se.Student_Name=’1’
Q29) Write a query to display employee name who is teaching EDC?

Ans.

1
2
3
4
5
6
7
SELECT E.Emp_Name
FROM Employee E
INNER JOIN Course Instructor Ci
ON E.Emp_No=Ci.Emp_No
INNER JOIN Course C
ON Ci.Course_ID=C.Course_ID
WHERE C.Course_Name=’EDC’
Q30) Write a query to display employee details along with dept name who are staying in Hyderabad?

Ans.

1
2
3
4
5
6
7
8
9
SELECT E.Emp_No, Emp_Name, D.Dept_Name
FROM Employee E
INNER JOIN Address A
ON E.Emp_No=A.Emp_No
INNER JOIN Instructor I
ON A.Emp_No=I.Emp_No
INNER JOIN Department D
ON I.Dept_No=D.Dept_No
WHERE A.Address=’hyderabad’

 

Emp_NoEmp_NameSalaryAgeDept_Name
101Anil20,00022ECE
102Sunil2300023EEE
103Vijay3200024CSE

Using Range Operator:: BETWEEN, NOT BETWEEN

Q31) Write a Query to display employee details whose salary > 20000 and whose age >23?

Ans.

1
2
SELECT * FROM Employee
WHERE Salary>20000 AND Age>23;
Q32) Write a Query to display employee details whose salary >20000 and who is working in the ECE department?

Ans.

1
2
SELECT * FROM Employee
WHERE Salary>20000 AND Dept_Name=’ECE’
Q33) Write a Query to display employee details whose age is BETWEEN 18 and 22?

Ans.

1
2
SELECT * FROM Employee Details
WHERE Age BETWEEN 18 AND 22;
Q34) Write a Query to display employee details whose salary range BETWEEN 20000 and 23000?

Ans.

1
2
SELECT * FROM Employee
WHERE Salary BETWEEN 20000 AND 23000;
Q35) Write a Query to display employee details whose age is NOT BETWEEN 18 & 22?

Ans.

1
2
SELECT * FROM Employee
WHERE Age NOT BETWEEN 18 AND 22;

Using String Operators:: LIKE, NOT LIKE

Q36) Write a Query to display employee details whose name starts with a?

Ans.

1
2
SELECT * FROM Employee
WHERE Emp_Name LIKE ‘a%’

a%  ----> starts with a
%a  ----> ends with a

Q37) Write a Query to display employee details and whose age>20 & whose name starts with a?

Ans.

1
2
SELECT * FROM Employee
WHERE Salary>20000 AND Age>20 AND Emp_Name LIKE ‘a%’
Q38) Write a Query to display employee details whose name not starts with a?

Ans.

1
2
SELECT * FROM employee
WHERE Emp_Name NOT LIKE ‘a%’
Posted by

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)