SQL BASIC REVIEW Examples and Group Activity

CREATE

-- Create a new database

CREATE DATABASE DemoDB;

 

-- Switch to the newly created database

USE DemoDB;

 

-- Create a new table named 'Employees'

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Department VARCHAR(50),

    Salary DECIMAL(10, 2),

    HireDate DATE

);

INSERT

-- Insert data into the 'Employees' table

INSERT INTO Employees (FirstName, LastName, Department, Salary, HireDate)

VALUES

('John', 'Doe', 'Engineering', 75000, '2020-01-15'),

('Jane', 'Smith', 'Marketing', 65000, '2019-03-12'),

('Emily', 'Jones', 'Finance', 80000, '2021-06-24'),

('Michael', 'Brown', 'Engineering', 72000, '2018-11-30');

SELECT

-- Select all data from the 'Employees' table

SELECT * FROM Employees;

 

-- Select specific columns from the 'Employees' table

SELECT FirstName, LastName, Department FROM Employees;

 

-- Select employees with a salary greater than 70000

SELECT * FROM Employees WHERE Salary > 70000;

 

-- Select employees hired after 2020

SELECT * FROM Employees WHERE HireDate > '2020-01-01';

UPDATE

-- Update the salary of 'John Doe' to 78000

UPDATE Employees

SET Salary = 78000

WHERE FirstName = 'John' AND LastName = 'Doe';

 

-- Give a 5% raise to all employees in the 'Engineering' department

UPDATE Employees

SET Salary = Salary * 1.05

WHERE Department = 'Engineering';

DELETE

-- Delete 'Emily Jones' from the 'Employees' table

DELETE FROM Employees

WHERE FirstName = 'Emily' AND LastName = 'Jones';

 

-- Delete all employees from the 'Marketing' department

DELETE FROM Employees

WHERE Department = 'Marketing';