Lab One: Introduction on Foundational SQL Commands
Setup: Data Definition Language (DDL)
1. CREATE TABLE
We will create three tables: Employees, Departments, and Projects.
-- 1. Create the Departments Table
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DeptID INT,
Salary DECIMAL(10, 2),
HireDate DATE,
CONSTRAINT fk_dept
FOREIGN KEY (DeptID)
REFERENCES Departments(DeptID)
);
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100) NOT NULL,
DeptID INT,
StartDate DATE
);
2. ALTER TABLE (Example)
Use this to change the structure of an existing table.
-- Example: Add a new column to the Employees table
ALTER TABLE Employees
ADD Email VARCHAR(100) UNIQUE;
3. DROP TABLE (For cleanup)
Use this to permanently delete a table and all its data.
-- Example: If you need to remove the Projects table
DROP TABLE Projects;
Data Manipulation Language (DML)
4. INSERT INTO
-- Insert data into Departments
INSERT INTO Departments (DeptID, DeptName, ManagerID) VALUES
(10, 'Sales', 101),
(20, 'Marketing', 105),
(30, 'HR', 103),
(40, 'IT', 104);
-- Insert data into Employees
INSERT INTO Employees (EmployeeID, FirstName, LastName, DeptID, Salary,
HireDate) VALUES
(101, 'Alice', 'Smith', 10, 75000.00, '2020-01-15'),
(102, 'Bob', 'Johnson', 10, 62000.00, '2021-05-20'),
(103, 'Charlie', 'Brown', 30, 58000.00, '2022-08-10'),
(104, 'Diana', 'Prince', 40, 95000.00, '2019-11-01'),
(105, 'Eve', 'Kardash', 20, 70000.00, '2021-03-25'),
(106, 'Frank', 'Jones', 40, 88000.00, '2023-01-05');
5. UPDATE
Exercise 1.1: Give all employees in the 'Sales' department (DeptID=10) a 5% raise.
UPDATE Employees SET Salary = Salary * 1.05 WHERE DeptID = 10;
6. DELETE FROM
Exercise 1.2: Delete the employee with EmployeeID = 102.
DELETE FROM Employees WHERE EmployeeID = 102;
Basic Querying and Filtering
These are the most fundamental Data Query Language (DQL) commands.
7. SELECT & FROM
Exercise 2.1: List the first name and hire date of every employee.
SELECT FirstName, HireDate FROM Employees;
Exercise 2.2: List all columns (*) and all rows from the Departments table.
SELECT * FROM Departments;
8. WHERE & Comparison Operators
Exercise 2.3: Find the FirstName and Salary of employees who earn more than $80,000.
SELECT FirstName, Salary FROM Employees WHERE Salary > 80000.00;
9. AND, OR, NOT (Logical Operators)
Exercise 2.4: Find employees in IT (DeptID=40) AND who were hired before 2020.
SELECT * FROM Employees WHERE DeptID = 40 AND HireDate < '2020-01-01';
10. ORDER BY (Sorting)
Exercise 2.5: List all employees, sorted by LastName in ascending order.
SELECT * FROM Employees ORDER BY LastName ASC;
-- ASC is the default, but good practice to include
Exercise 2.6: List all employees, sorted by Salary in descending order.
SELECT * FROM Employees ORDER BY Salary DESC;
11. DISTINCT
Exercise 2.7: List all unique salary values present in the Employees table.
SELECT DISTINCT Salary FROM Employees;
Advanced Filtering and Range
12. LIKE (Pattern Matching)
Exercise 3.1: Find all employees whose LastName starts with the letter 'J'.
SELECT FirstName, LastName FROM Employees WHERE LastName LIKE 'J%';
Exercise 3.2: Find all employees whose FirstName has 'e' as the second letter (e.g., Eve).
SELECT FirstName, LastName FROM Employees WHERE FirstName LIKE '_e%';
13. IN (Listing values)
Exercise 3.3: Find all employees who work in DeptID 10 or 30.
SELECT * FROM Employees WHERE DeptID IN (10, 30);
14. BETWEEN (Range)
Exercise 3.4: Find all employees with a Salary between $60,000 and $75,000 (inclusive).
SELECT FirstName, Salary FROM Employees WHERE Salary BETWEEN 60000.00 AND
75000.00;