Please visit, subscribe and share 10 Minutes Lectures in Computer Science
Showing posts with label SQL Exercise. Show all posts
Showing posts with label SQL Exercise. Show all posts

Write SQL statement for each of the following requirement

Question:

Consider the following relational schema
EMPLOYEE (EmpNo, Name, DOB, DepNo)
JOBHISTORY (EmpNo, Position, StartDate, EndDate, Salary)
where
EmpNo is the primary key for table EMPLOYEE
DepNo is the primary key for table DEPARTMENT
(EmpNo, StartDate) is the composite primary key for table JOBHISTORY
Write an SQL statement for each of the following queries.
a) List all the employees (by name) who are working at the Finance department.
b) List all the employees who are working at the same department as Michael.
c) Find the number of employees currently working at each department (by name).
d) List all the employees (by Name) who are currently working as Software Engineer.
e) Count the average salary for all the employees who are currently working at the Information Technology department.

Solution:

a) List all the employees (by name) who are working at the Finance department.

Expected resultNames of employees of Finance department
Where to get? – from EMPLOYEE and DEPARTMENT tables
How?join both tables on DepNo attribute.
WHERE clause conditions – join condition Employee.DepNo = Department.DepNo  and Dname = ‘Finance’
Query:
SELECT Name FROM Employee, Department
WHERE Employee.DepNo = Department.DepNo
AND DName = ‘Finance’;

b) List all the employees who are working at the same department as Michael.

Expected resultNames of employees of Michael’s department
Where to get? – from EMPLOYEE table
How? – use sub-query.
WHERE clause conditions – filter Michael’s department using Name = ‘Michael’ in the sub-query.
Query:
SELECT Name FROM Employee
WHERE DepNo = (SELECT DepNo FROM Employee
WHERE Name = ‘Michael’);

c) Find the number of employees currently working at each department (by name).

Expected resultCount of employees at each department (individual count)
Where to get? – from EMPLOYEE and DEPARTMENT tables
How? – use join and aggregate function count().
WHERE clause conditions – join condition Employee.DepNo = Department.DepNo.
Any other clauses to be usedGROUP BY clause
Query:
SELECT DName, Count(*) FROM Employee, Department
WHERE Employee.DepNo = Department.DepNo
GROUP BY DName;

d) List all the employees (by Name) who are currently working as Software Engineer.

Expected resultNames of employees working as software engineers
Where to get? – from EMPLOYEE and JOBHISTORY tables
How? – use join.
WHERE clause conditions – join condition Employee.EmpNo = Jobhistory.EmpNo, Position = ‘Software Engineer’, and EndDate is NULL of empty.
Any other clauses to be used – No
Query:
SELECT Name FROM Employee, Jobhistory
WHERE Employee.EmpNo = Jobhistory.EmpNo
AND Position = ‘Software Engineer’
AND EndDate = ‘’;

e) Count the average salary for all the employees who are currently working at the Information Technology department.

Expected resultAverage salary of employees of Information Technology department
Where to get? – from EMPLOYEE, DEPARTMENT, and JOBHISTORY tables
How? – use join and aggregate function avg().
WHERE clause conditions – join condition Employee.DepNo = Department.DepNo, Employee.EmpNo = Jobhistory.EmpNo, DName = ‘Information Technology’ and EndDate is NULL or empty [we need to include only current salary. That means salary of current position].
Any other clauses to be used – No
Query:
SELECT Avg(*) FROM Employee, Department, Jobhistory
WHERE Employee.DepNo = Department.DepNo
AND Employee.EmpNo = Jobhistory.EmpNo
AND DName = ‘Information Technology’
AND EndDate = ‘’;

***********

Go to Natural join in DBMS page (SQL)
Go to Equi-join in DBMS page (SQL)

Go to Solved exercise 1
Go to Solved exercise 2
Go to Solved exercise 3

Sunday, February 18, 2018

How does SQL evaluate conditions for tuples (records) containing NULLS?

How does SQL evaluate conditions for tuples (records) containing NULLS?

Question:
For a tuple {‘C110’, ‘Abhishek’, NULL, ‘Mumbai’, 7867564534}, what will be the result of the following WHERE clauses? Choose the appropriate from TRUE, FALSE, and UNKNOWN.

1. WHERE Age > 18
2. WHERE Age > 18 OR Name = 'Abhishek'
3. WHERE Age > 18 OR NOT (Age > 18)
4. WHERE Phone = 7867564534 AND Age = ‘NULL’
5. WHERE Age is NULL

SQL essentially uses 3-valued logic, where comparisons involving NULLs evaluate to a third value which is UNKNOWN.

1. WHERE Age > 18 – UNKNOWN
Why? Age is NULL. NULL compared with any value is UNKNOWN.

2. WHERE Age > 18 OR Name = ‘Abhishek’ – TRUE
Why? Age is NULL. But Age > 18 is not the only condition. It is ORed with the condition Name = ‘Abhishek’ results in TRUE. For the condition involving logical operator OR, if any one of the conditions return TRUE then the answer becomes TRUE. Hence the answer is TRUE.

3. WHERE Age > 18 OR NOT (Age > 18) – UNKNOWN
Why? Both conditions involve Age attribute and Age is NULL. Hence the answer is UNKNOWN.

4. WHERE Phone = 7867564534 AND Age = ‘NULL’ – UNKNOWN
Why? In the condition Age = ‘NULL’, yet the value of Age is NULL. Hence the result is UNKNOWN.

5. WHERE Age is NULL – TRUE
Why? In SQL, an tuple with NULL value for an attribute can be identified using the syntax ‘attribute IS NULL’. If the value of that particular attribute is NULL, then the result is TRUE.

**************

How does SQL evaluate conditions for tuples (records) containing NULLS?
NULL value logic
Three valued logic
How NULL values are evaluated in SQL ?
NULL values are evaluated to TRUE, FALSE, UNKNOWN

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

data recovery