CSE3522 DBMS SQL Query Handout
SQL Query Practice
CSE3522 DBMS Company Database
All queries below run on three tables: employee, department and dependent. Most examples
use the employee table.
1. The Database Schema
CREATE TABLE department (
dname VARCHAR(15),
dnumber INT NOT NULL,
mgrssn INT,
mgrstartdate DATE,
PRIMARY KEY (dnumber)
);
CREATE TABLE employee (
fname VARCHAR(15),
minit VARCHAR(2),
lname VARCHAR(15),
ssn INT(12) NOT NULL,
bdate DATE,
address VARCHAR(35),
gender VARCHAR(1),
salary INT(7) NOT NULL,
superssn INT(12),
dno INT NOT NULL,
PRIMARY KEY (ssn),
CONSTRAINT fk_dno_dnumber FOREIGN KEY (dno)
REFERENCES department (dnumber)
);
CREATE TABLE dependent (
essn INT,
dependent_name VARCHAR(15),
gender VARCHAR(1),
bdate DATE,
relationship VARCHAR(12),
PRIMARY KEY (essn, dependent_name),
CONSTRAINT fk_essn_ssn FOREIGN KEY (essn)
REFERENCES employee (ssn)
);
2. Comparison Conditions in WHERE
The WHERE clause keeps only the rows that satisfy a condition. Comparison operators: = <>
(or !=) < > <= >=.
What we are querying: Employees who earn more than 30,000.
SELECT fname, lname, salary
FROM employee
WHERE salary > 30000;
What we are querying: Employees who work in department 5 (equality test).
SELECT fname, lname
FROM employee
1
CSE3522 DBMS SQL Query Handout
WHERE dno = 5;
What we are querying: Employees who do NOT work in department 5 (the <> operator).
SELECT fname, lname, dno
FROM employee
WHERE dno <> 5;
3. Logical Operators: AND, OR, NOT
Combine conditions with AND (both must hold), OR (either holds), and NOT (negation).
What we are querying: Employees in department 5 who also earn more than 30,000.
SELECT fname, lname, salary
FROM employee
WHERE dno = 5 AND salary > 30000;
What we are querying: Employees who work in department 1 or department 4.
SELECT fname, lname, dno
FROM employee
WHERE dno = 1 OR dno = 4;
What we are querying: Employees who are not female.
SELECT fname, lname, gender
FROM employee
WHERE NOT gender = ’F’;
4. Range Search Condition: BETWEEN
BETWEEN ... AND ... tests whether a value falls within a range, with both endpoints included.
NOT BETWEEN is the opposite.
What we are querying: Employees whose salary is between 40,000 and 50,000 (inclusive).
SELECT fname, lname
FROM employee
WHERE salary BETWEEN 40000 AND 50000;
What we are querying: Employees whose salary is outside that range.
SELECT fname, lname
FROM employee
WHERE salary NOT BETWEEN 40000 AND 50000;
What we are querying: The same range written with >= and <= — more flexible when you
need an open-ended range.
SELECT fname, lname
FROM employee
WHERE salary >= 40000 AND salary <= 50000;
2
CSE3522 DBMS SQL Query Handout
5. Set Membership: IN and NOT IN
IN tests whether a value matches any item in a list; NOT IN negates it.
What we are querying: Employees whose salary is exactly 30,000 or 40,000.
SELECT fname, lname
FROM employee
WHERE salary IN (30000, 40000);
What we are querying: Employees whose salary is neither 30,000 nor 40,000.
SELECT fname, lname
FROM employee
WHERE salary NOT IN (30000, 40000);
6. Calculated Fields
You can perform arithmetic (+ - * /) on columns right inside the SELECT list. The result is
computed for display and does not change the stored data.
What we are querying: Divide the salary by 5, for employees in department 5.
SELECT (salary / 5)
FROM employee
WHERE dno = 5;
What we are querying: The same calculation, but with a readable column name using AS.
SELECT (salary / 5) AS salary_divide_by_five
FROM employee;
7. The LIMIT Clause
LIMIT restricts how many rows are returned.
What we are querying: Display the first three employees.
SELECT fname, lname, salary
FROM employee
LIMIT 3;
8. Aggregate Functions: MIN and MAX
Aggregate functions compute a single value over many rows.
What we are querying: The minimum salary among all employees.
SELECT MIN(salary) AS Minimum_Salary
FROM employee;
What we are querying: The maximum salary among all employees.
SELECT MAX(salary) AS Maximum_Salary
FROM employee;
3
CSE3522 DBMS SQL Query Handout
9. DISTINCT
DISTINCT removes duplicate values from the result.
What we are querying: All distinct department numbers in which employees work.
SELECT DISTINCT dno
FROM employee;
What we are querying: The distinct gender values that appear in the table.
SELECT DISTINCT gender
FROM employee;
10. ORDER BY
ORDER BY sorts the result. The default is ascending (ASC); use DESC for descending.
What we are querying: Employees ordered by salary, lowest to highest (default order).
SELECT fname, lname, salary
FROM employee
ORDER BY salary;
What we are querying: Same result, stated explicitly with ASC.
SELECT fname, lname, salary
FROM employee
ORDER BY salary ASC;
What we are querying: Employees ordered by salary, highest to lowest.
SELECT fname, lname, salary
FROM employee
ORDER BY salary DESC;
What we are querying: Sort by department ascending, and within each department by salary
descending.
SELECT fname, lname, dno, salary
FROM employee
ORDER BY dno ASC, salary DESC;
11. GROUP BY
GROUP BY collapses rows that share a value into one group, so aggregate functions can be
computed per group.
What we are querying: The number of employees in each department.
SELECT dno, COUNT(*) AS Total_Employees
FROM employee
GROUP BY dno;
What we are querying: The average salary of each department.
SELECT dno, AVG(salary) AS Average_Salary
FROM employee
GROUP BY dno;
4
CSE3522 DBMS SQL Query Handout
12. HAVING
WHERE filters individual rows; HAVING filters groups after GROUP BY.
What we are querying: Only departments having more than 2 employees.
SELECT dno, COUNT(*) AS Total_Employees
FROM employee
GROUP BY dno
HAVING COUNT(*) > 2;
What we are querying: Departments whose average salary is greater than 30,000.
SELECT dno, AVG(salary) AS Average_Salary
FROM employee
GROUP BY dno
HAVING AVG(salary) > 30000;
What we are querying: Departments having more than one employee, sorted by average
salary (highest first) — HAVING and ORDER BY together.
SELECT dno, AVG(salary) AS Average_Salary
FROM employee
GROUP BY dno
HAVING COUNT(*) > 1
ORDER BY Average_Salary DESC;
13. Logical Order of Execution
Even though we write SELECT first, the database executes the clauses in this order:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
This is why WHERE cannot use a column alias defined in SELECT, but ORDER BY can.
14. LIMIT with OFFSET
OFFSET skips a number of rows before LIMIT starts counting.
SELECT column_name
FROM table_name
LIMIT number_of_rows
OFFSET number_to_skip;
What we are querying: Skip the first 2 employees, then show the next 3.
SELECT fname, lname, salary
FROM employee
LIMIT 3 OFFSET 2;
15. ROUND()
ROUND(number, decimal places) rounds a numeric value.
What we are querying: The average salary of all employees, rounded to 2 decimal places.
SELECT ROUND(AVG(salary), 2) AS Average_Salary
FROM employee;
5
CSE3522 DBMS SQL Query Handout
16. Column Aliases with AS
AS renames a column in the output only; the real column name is unchanged.
What we are querying: Show the salary under the display name my sal for high earners.
SELECT salary AS my_sal
FROM employee
WHERE salary > 1000;
17. Pattern Matching with LIKE
LIKE tests a string against a pattern. Two wildcards:
• % matches any string of zero or more characters (length does not matter).
• matches exactly one character (position and length matter).
Using the % wildcard
What we are querying: Last name ends with ‘H’.
SELECT fname, lname
FROM employee
WHERE lname LIKE ’%H’;
What we are querying: First name starts with ‘J’.
SELECT fname, lname
FROM employee
WHERE fname LIKE ’J%’;
What we are querying: First name contains the letter ‘A’ anywhere.
SELECT fname, lname
FROM employee
WHERE fname LIKE ’%A%’;
A note on case sensitivity
Depending on the database and the column’s collation, LIKE can be case-sensitive. If the
address column stores its data in capital letters, a lowercase pattern finds nothing — the
pattern must match the case of the stored data.
What we are querying: Try these two one after another and compare the results.
SELECT fname, lname, address
FROM employee
WHERE address LIKE ’%houston%’; -- may return NO rows
SELECT fname, lname, address
FROM employee
WHERE address LIKE ’%HOUSTON%’; -- matches the stored capitals
The second query works when the data is stored in all capitals, while the first returns nothing.
(You can also use UPPER() or LOWER() to normalise both sides before comparing.)
6
CSE3522 DBMS SQL Query Handout
Using the wildcard
What we are querying: First name is 4 letters long and ends with ‘OHN’ (e.g. John): one
character, then OHN.
SELECT fname, lname
FROM employee
WHERE fname LIKE ’_OHN’;
What we are querying: Last name is exactly 5 letters and ends with ‘TH’: three characters,
then TH.
SELECT fname, lname
FROM employee
WHERE lname LIKE ’___TH’;
What we are querying: First name is 4 letters and starts with ‘JO’: JO, then two characters.
SELECT fname
FROM employee
WHERE fname LIKE ’JO__’;
Reminder: % fixes position but not length; fixes both position and length (one underscore = exactly
one character).