SQL Worksheets

Worksheets

1. Introduction to the Emp-dept case study	View
2. Some simple SQL Select statements	View
3. Using Functions	View
4. Selecting ROWS to display	View
5. Multiple tables	View
6. Summarising and Grouping	View
7. Handling Dates	View
8. Inner and Outer Joins	View

Arithmetic Expressions	Show the annual salary (for 12 months) for each Employee
Column Alias	Name the computed field
DESCRIBE TABLE	To view the definition of a table.
Date Comparison	Comparing dates
Function AVG()	Average, min and max.
Function COUNT()	Show the number of Employees
Function CURRENT-DATE()	Simple expressions: Current Date
Function CURRENT-TIME()	Show the Current time
Function DATE_ADD()	To compute dates.
Function DAYOFMONTH()	Extracting parts of dates
Function IFNULL()	Convert Nulls to zero.
Function MAX()	Average, min and max.
Function MIN()	Average, min and max.
Function MONTH()	Extracting parts of dates
Function NOT()	List the names and department numbers of employees who do NOT work in department 20.
Function ROUND()	How many weeks has each employee worked?
Function TO_DAYS()	To compute date intervals.
Function YEAR()	Extracting parts of dates
JOIN	List the contents of two tables
Multi-table Join	Related data in more than two tables.
Operator =	List the data for employees in department 10
Operator >	Show the details of all Employees who have a salary greater than 2000.
Operator AND	Complex expressions
Operator BETWEEN	List the name and salary of Clerks who earn between 900 and 1000.
Operator IN	List the names of Analysts and Managers in Department 20 and 30.
Operator INTERVAL	To compute dates.
Operator IS NULL	NULL fields
Operator LIKE	LIKE operator
Operator OR	List the names of all Analysts and the Managers in Department 30.
Relation PRODUCT	List the contents of two tables
SELECT .. FROM	Show all Employees
SELECT .. FROM .. ORDER BY	Ordering the rows of the output
SELECT .. FROM .. ORDER BY .. DESC	Ordering in Descending order
SELECT .. FROM .. WHERE	List the data for employees in department 10
SELECT .. INNER JOIN.. ON	Show all Employee names with their Department location - format 3
SELECT .. NATURAL JOIN	Show all Employee names with their Department location - format 2
SELECT .. OUTER LEFT JOIN.. ON	Show -ALL- Department locations with their employees
SELECT ... GROUP BY..	Summarise groups of data.
SELECT ... GROUP BY.. HAVING ..	The 'HAVING' clause.
SELECT ... LIMIT n.	Find the employee whose name is first in the alphabet (2).
SELECT DISTINCT .. FROM ..	Show distinct department numbers in the emp table.
SHOW TABLES	To list the tables in a database
Self Join	Table aliases - 1.
Table Alias	Table aliases - 1.
Theta Join	List every employee by name with their monthly salary and salary grade.

SQL Worksheets tagged DSA

Index

Worksheets

Subject Index