SQL Practice In class - My SQL solution

Q1

CREATE TABLE Employee
(
FirstName VARCHAR(30),
LastName VARCHAR(30),
email VARCHAR(30),
DOB DATETIME,
Phone VARCHAR(12)
)

INSERT INTO Employee
VALUES
('John', 'Smith', 'John.Smith@yahoo.com', '2/4/1968','626 222-222')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('Steven', 'Goldfish', 'goldfish@fishhere.net', '4/4/1974', '323 455-4545')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('Paula', 'Brown', 'pb@herowndomain.org', '5/24/1978', '416 323-3232')

INSERT INTO Employee
                      (FirstName, LastName, email, DOB, Phone)
VALUES     ('James', 'Smith', 'jim@supergig.co.uk', '10/20/1980', '416 323-8888')

Q2

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (LastName LIKE 'SMITH')

Q3

SELECT     COUNT(*) AS Num_of_Emp_Like_SMITH
FROM         Employee
WHERE     (LastName LIKE 'SMITH')

Q4

SELECT     LastName, COUNT(*) AS NumberOfEmp
FROM         Employee
GROUP BY LastName
ORDER BY LastName DESC

Q5

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (DOB >= '01/01/1970')

Q6

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (Phone LIKE '416%')

Q7

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (email LIKE '%.%@%.%')

Q8

UPDATE    Employee
SET              DOB = '05/10/1974'
WHERE     (LastName = 'Goldfish') AND (FirstName = 'Steven')

Q9

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
ORDER BY DOB DESC

Q10

SELECT     FirstName, LastName, email, DOB, Phone
FROM         Employee
WHERE     (FirstName LIKE '_____%')

Q11

ALTER TABLE Employee ADD Id INT IDENTITY
CONSTRAINT pk_ID PRIMARY KEY(Id)

Q12

SELECT     FirstName, LastName, email, DOB, Phone, Id
FROM         Employee
WHERE     (DATEPART(m, DOB) = DATEPART(m, GETDATE()))

Q13

create table EmployeeHours
(
empFName Varchar(30),
empLName Varchar(30),
Date DATETIME,
Hours 



insert into employeehours
VALUES
('John', 'Smith', '5/6/2004', 8)

insert into employeehours
VALUES
('John', 'Smith', '5/7/2004', 9)

insert into employeehours
VALUES
('Steven', 'Goldfish', '5/7/2004', 8)

insert into employeehours
VALUES
('James', 'Smith', '5/7/2004', 9)

insert into employeehours
VALUES
('John', 'Smith', '5/8/2004', 8)

insert into employeehours
VALUES
('James', 'Smith', '5/8/2004', 8)

Q13


Alter table EmployeeHours
add EmployeeID INT
CONSTRAINT fk_ID Foreign Key (EmployeeID) References Employee(id)

Q14

CREATE PROCEDURE UpdateEmpId
    
AS
    UPDATE    EmployeeHours
    SET       EmployeeId =
             (SELECT     Id
              FROM       Employee
WHERE      (EmployeeHours.EmpFName = FirstName) AND (EmployeeHours.EmpLName = LastName))