DBSD

In-Class Test (Slot 9:30)

/*1st task*/

Show list of marital statuses and gender for employees. Sort results by marital status in descending order (use table HumanResources.Employee)

select e.MaritalStatus, e.Gender

from HumanResources.Employee e

order by MaritalStatus desc;

/*2nd task*/

Select all the information about products whose product number starts with letters ''BE". Sort results by product name in ascending order (Use table Product.Product)

select * from Production.Product p

where p.ProductNumber like '%BE%'

order by p.Name;

/*3rd task*/

Show customers who has title 'Mr', territoryId equal to 5 and additionalContactInfo not null. In the output show, account number, storeId, title, first, and last name. Provide meaningful names for the columns in the results . Sort results by store id in descending order and then by first name in ascending order. (Use tables Person.Person and Sales.Customer)

select c.AccountNumber, c.StoreID, p.Title, p.FirstName, p.LastName

from Person.Person p join Sales.Customer c on p.BusinessEntityID = c.PersonID

where p.Title = 'Mr.' and c.TerritoryID = 5 and p.AdditionalContactInfo is not null order by c.StoreID desc, p.FirstName;

/*4th task*/

For each product show color, number of products with this color, and min price for product of each color. Filter out products that have cost more than 1000. Show only those colors that have minimum price more than 0. Provide meaningful names for the columns in the result. Sort results by min price in ascending order. (Use table Product.Product)

select distinct p.Color,

(select count(ProductID)

from Production.Product pp

where Color = p.Color)

from Production.Product p where p.StandardCost > 1000;

/*5th task*/

Show all employees who have number of sick leave hours more than the average number of sick leave hours among all employees. In the results display job title =, employee first, last, and middle names in one column separated with a space, sick leave hours of the employee, ans average sick leave hours of all employees. Provide meaningful names for the columns in the results. Sort results by the employee sick leave hours in descending order. (Use tables HumanResourcess.Employee and Person.Person)

select e.BusinessEntityID, e.JobTitle, p.FirstName,

(select avg(SickLeaveHours)

from HumanResources.Employee as "Average sick hours"

from HumanResources

/*7th task*/

Show all products with names containing word ''Clothing", for each product display product name, sub category name, category name, and number of photos of the product. Provide meaningful names for the columns in the results. Sort results by product name in ascending order and then by number of product photos. (Use tables Production.Product, Production.ProductReview, production.ProductsSubcategory, production.ProductCategory

select p.Name as "Product Name", sc.Name as "Sub category name", c.Name as "Category Name", r.Comments

from Production.Product p join Production.ProductSubcategory sc on p.ProductSubcategoryID = sc.ProductSubcategoryID join

Production.ProductCategory c on sc.ProductCategoryID = c.ProductCategoryID join

Production.ProductReview r on r.ProductID = r.ProductID;

MOCK

You are given an AdventureWorks2012 database file located in MS SQL server. Your task to create the following queries and save them in one file under name YourID_DBSDTest1 on desktop folder DBSD.

Number all your queries.

1. Count how many different jobtitles exist among employees.. [5 marks]

Select count (jobtitle) from HumanResources.Employee

2. Retrieve all information from Product table regarding all products not containing Metal in their names.

Select * from Production.Product

Where name not like '%metal%'

3. Show JobTitle, HireDate, FirstName, LastName of those employees who were hired before first of January 2002. Order records by HireDate.

Select jobtitle, HireDate, pp.FirstName, pp.LastName from HumanResources.Employee he join person.Person pp on he.BusinessEntityID=pp.BusinessEntityID

where hiredate<'2002-01-01'

order by HireDate

4. Display all product names and corresponding culture names.

Select pp.Name as product, pc.Name as model

from Production.Product pp

join production.ProductModelProductDescriptionCulture pm on pp.ProductModelID=pm.ProductModelID

join Production.Culture pc on pm.CultureID=pc.CultureID

5. Show JobTitle, BirthDate, Age of Employee, FirstName, LastName of those Employees who are older that 60. Order records by years.

Select jobtitle, BirthDate, , pp.FirstName, pp.LastName from HumanResources.Employee he

join person.Person pp on he.BusinessEntityID=pp.BusinessEntityID

where DATEDIFF(YEAR, BirthDate, GETDATE())>60

order by years

6. Show Rates of Employees’ salary and corresponding JobTitle when Rate is lower than average rate.

select e.JobTitle, rate from HumanResources.EmployeePayHistory eph join HumanResources.Employee e on

eph.BusinessEntityID=e.BusinessEntityID

where rate<(select avg(eph.Rate) from HumanResources.EmployeePayHistory eph)

7. Show the number of products per subcategory sorted in descending order only for those subcategories, which have more than 20 products.

Select count(pp.productID), ps.Name

from Production.Product pp join Production.ProductSubcategory ps

on pp.ProductSubcategoryID=ps.ProductSubcategoryID

group by ps.Name

having count(pp.productID)>20

order by count(pp.productID) desc

8. Create views on any two queries and comment whether they are updatable or not. [10 marks]

9. Show list of employees ordered by department, employee rate and minimum rate for department this employee works at. For each employee show the difference between lowest rate in the department where employee works and his own salary.

Select pp.FirstName, pp.LastName, rate, min(rate) over (partition by hd.name) as [minimum in its department], rate-min(rate) over (partition by hd.name) as difference from Person.Person pp

join HumanResources.EmployeePayHistory hep

on pp.BusinessEntityID=hep.BusinessEntityID

join HumanResources.EmployeeDepartmentHistory hed

on hed.BusinessEntityID=pp.BusinessEntityID

join HumanResources.Department hd

on hd.DepartmentID=hed.DepartmentID

order by hd.Name, rate

10. Retrieve the BusinessEntityID, LoginID, JobTitle for those employees who are job candidates as well. You should use INTERSECT operator to combine the two queries and CTE.

WITH

cteCandidates (BusinessEntityID)

(

SELECT BusinessEntityID

FROM HumanResources.Employee

INTERSECT

SELECT BusinessEntityID

FROM HumanResources.JobCandidate

)

SELECT

c.BusinessEntityID,

e.LoginID,

e.JobTitle

FROM

HumanResources.Employee AS e

INNER JOIN cteCandidates AS c

ON e.BusinessEntityID = c.BusinessEntityID

ORDER BY

c.BusinessEntityID;

Seminar 4

create database t4_123

use t4_123

create table department

(

id int not null,

title varchar(100),

location varchar(50)

)

drop table department

create table department

(

id int primary key,

title varchar(100),

location varchar(50)

)

drop table department

create table department

(

id int,

title varchar(100),

location varchar(50),

constraint pk_emp_id

primary key(id)

)

create table department2

(

id int not null,

title varchar(100) not null,

location varchar(50)

)

create table department3

(

id int,

title varchar(100),

location varchar(50),

constraint uq_department_title

unique(title),

constraint uq_department_id_location

unique(id, location)

)

drop table department4

create table department4

(

id int,

title varchar(100),

location varchar(50),

constraint pk_department4_id_title

primary key (id, title)

)

EXEC sp_rename 'department4', 'department5'

EXEC sp_rename 'department5.id', 'did', 'COLUMN'

drop table department2

drop table department3

drop table department5

----Employee table-----

create table employee

(

id int not null,

firstname varchar(25) not null,

lastname varchar(25) not null,

dob date,

address varchar(200),

email varchar(100) not null,

phone varchar(20),

salary decimal(10,2),

dateemployed datetime not null default getdate(),

departmentid int,

constraint pk_employee_id primary key(id),

constraint uq_employee_email unique(email)

)

alter table employee

alter column

firstname varchar(60) not null

alter table employee

alter column

lastname varchar(60) not null

alter table employee

alter column dob date not null

alter table employee

drop column lastname

alter table employee

add lastname varchar(60) not null

alter table employee

drop constraint pk_employee_id

alter table employee

drop constraint uq_employee_email

alter table employee

add

constraint pk_employee_id primary key(id)

alter table employee

add

constraint uq_employee_email unique(email)

alter table employee

drop constraint

fk_employee_departmentid

alter table employee

add constraint

fk_employee_departmentid

foreign key(departmentid)

references department(id)

on delete set null

on update cascade

alter table employee

add constraint

ck_employee_salary

check(salary > 0 and salary < 5000)

Seminar 5

1. List all the employees whose salary is between 800 and 1500, show employee name, salary, and department number.

select ename, sal, deptno

from EMP

where sal between 800 and 1500

2. Display all the different job titles in table EMP in ascending order. Make sure that the list contains only unique job titles. Rename the column job to “Job title”.

select distinct job as 'Job title'

from EMP

order by job

3. List all the details of employees in departments 10 and 20. Sort the results by employee name in descending order.

select *

from EMP

where deptno in (10,20)

order by ename asc

4. List all employees who were employed earlier then ‘2010-01-01’. Display their names and hiredates. Order results by hiredate in descending order and then by employee name in ascending order.

select ename, hiredate

from emp

where hiredate < '2010-01-01'

order by hiredate desc, ename

5. Display all the employees who were recruited during 2006, giving their name, department number and hiredate.

select ename, deptno, hiredate

from emp

where hiredate between '2006-01-01' and '2006-12-31'

6. List all employees names whose name starts with ‘A’ or contain letter ‘L’. Rename ename column to “Names starting with A or containing L”

select ename 'Names starting with A or containing L'

from emp

where ename like 'A%' or ename like '%L%'

7. List all employees with names with letter ‘r’ at 3rd position: e.g.: ‘Mark’.

select ename

from emp

where ename like '__r%'

8. List all Analysts and Salesmen who earn more than 1250

select ename, job, sal

from emp

where (job = 'analyst' or job = 'salesman')

and sal > 1250

9. List employees who are managed by managers with ids 7782, 7566, 7902. Do the opposite: list employees who are not managed by these managers.

select ename, manager

from emp

where manager in (7782, 7566, 7902)

select ename, manager

from emp

where manager not in (7782, 7566, 7902)

10. List all employees who either work as a Clerk or earn less than 1000 or were hired before 2005-01-01.

select *

from emp

where job = 'clerk' or sal < 1000 or hiredate < '2005-01-01'

11. List all clerks in departments 20 and 30 earning more than 1000 and all salesmen in department 30 earning 1500 or more

select *

from emp

where (job = 'clerk' and deptno in(20,30) and sal > 1000)

or (job = 'salesman' and deptno = 30 and sal >=1500)

12. Write a query that will return inversed results for task 11. Hint: use NOT.

Additional tasks and homework

13. List all the employees whose names have ‘TH’ or ‘LL’ in them.

select ename

from emp

where ename like '%TH%' or ename like '%LL%'

14. List all employees whose name is longer than 4 characters and ends with ‘H’ or ‘T’.

select ename

from emp

where (ename like '____%' and ename like '%H' or ename like '%T')

15. List names and jobs of all clerks in department 20, format the output to look like “JOHN works as a clerk”. Use string concatenation operator “+” or CONCAT() function and LOWER()/UPPER() functions (see examples in Appendix) to change job and ename name case.

select ename + ' ' + 'works as' + ' ' + job

from emp

where (job = 'clerk' and deptno = 20)

Seminar 6

JOIN

1. Find the name, salary, and department name for employees who work in departments located in Dallas.

select e.ename as 'Employee Name', e.sal 'Employee Salary', d.dname 'Department Name'

from emp e join dept d on e.deptno = d.deptno

where d.loc = 'Dallas'

2. For all employees display employee name and name of the department where employee works. Provide meaningful names for the columns in the results, e.g. dname -> “Department name”.

select e.ename as 'Employee Name', d.dname 'Department Name'

from emp e join dept d on e.deptno = d.deptno

3. List employee names and names of their managers. Provide meaningful names for the columns in the results.

select e.ename 'Employee Name', e.manager 'Manager Assigned'

from emp e join emp m on e.manager = m.empno

order by 'Manager Assigned'