ICTPRG425 – Use structured query language
Student Guide and Assessment
Part 1: Write simple SQL statements to retrieve and sort data
a. Write a SQL statement to retrieve all data from Departments table.
b. Write a SQL statement to retrieve a list of title from Titles table. Order the list by
Part 2: Write SQL statements to retrieve specific data
a. Retrieve a list of employees with First Name “Mary”
b. Retrieve a list of male employees hired in year 2000
c. Retrieve all employees with employee number from 13000 to 13010
d. Retrieve all Finance and Marketing employees hired in year 2000
e. Retrieve a list of employees hired in year 2000 and having First Name starting
with “A” or “B”
f. Retrieve a list of unique position from the Titles table with End date not equal to
Part 3: Write SQL statements that use SQL functions
a. Find out the total number of employees in the company.
b. Retrieve first name, last name and hired year of all employees. The last name
must be in upper case.
c. Find out the maximum salary paid to employees in the company.
d. Retrieve a list of Finance employees with birthday in the month of June.
e. Find out the number of salary increments for employee 10001.
Part 4: Write SQL statements that use aggregation, filtering and data from
a. Find out the number of male and female employees in the company
b. Find out the number of employees in each department. Sort the list by highest
number of employees
Part 5: Write SQL statements that retrieve data from multiple tables
a. Retrieve a list of employee with the following details: Emp_no, First Name, Last
Name, Hire date, Department, Position, Salary
b. Retrieve a list of employees with emp_no between 109995 and 110200. Use
RIGHT or LEFT join to identify any managers in the list.
c. Retrieve a list of all department names and job titles. Use FULL join or UNION in
Part 6: Write and execute SQL sub-queries
a. Retrieve a list of employees with salary less than Vidar Turnbull ‘s salary
b. Retrieve a list of employees in Marketing and Research departments. ( Use a sub
c. Find out the number of employees with more than average salary.
d. Find out the highest paid employee in the company
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx