Scribd
Upload
11 views2 pages

SQL Constraints and Queries Overview

The document contains SQL commands for altering tables in a database, including adding foreign key constraints, check constraints, and unique constraints for various tables such as Employees, Departments, Projects, Attendance, and Salaries. It also includes several SELECT queries to retrieve average bonuses, employee details, and attendance records based on specific conditions. Overall, the document focuses on database schema modifications and data retrieval operations.

Uploaded by

Sinshi سينشي
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views2 pages

SQL Constraints and Queries Overview

The document contains SQL commands for altering tables in a database, including adding foreign key constraints, check constraints, and unique constraints for various tables such as Employees, Departments, Projects, Attendance, and Salaries. It also includes several SELECT queries to retrieve average bonuses, employee details, and attendance records based on specific conditions. Overall, the document focuses on database schema modifications and data retrieval operations.

Uploaded by

Sinshi سينشي
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

1 - ALTER TABLE Employees

ADD CONSTRAINT FK_Employees_Departments


FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

2 - ALTER TABLE Projects


ADD CONSTRAINT FK_Projects_Departments
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

3 - ALTER TABLE Attendance


ADD CONSTRAINT FK_Attendance_Employees
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);

4 - ALTER TABLE Salaries


ADD CONSTRAINT FK_Salaries_Employees
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID);

5 - ALTER TABLE Projects


ADD CONSTRAINT CHK_Projects_Budget
CHECK (Budget > 0);

6- ALTER TABLE Salaries


ADD CONSTRAINT CHK_Salaries_BaseSalary
CHECK (BaseSalary BETWEEN 30000 AND 200000);

7 - ALTER TABLE Attendance


ADD CONSTRAINT CHK_Attendance_CheckOutTime
CHECK (CheckOutTime > CheckInTime);

8 - ALTER TABLE Employees


ADD CONSTRAINT UQ_Employees_Email
UNIQUE (Email);

9 - ALTER TABLE Employees


ADD CONSTRAINT UQ_Employees_Phone
UNIQUE (Phone);

10 - SELECT AVG(Bonus) AS AverageBonus


FROM Salaries;

11 - SELECT [Link], [Link], [Link]


FROM Employees e
JOIN Departments d ON [Link] = [Link]
WHERE [Link] = 'Quality Assurance';

12 - SELECT *
FROM Employees
WHERE LEN(FirstName) = 4;
13 - SELECT *
FROM Employees
WHERE FirstName LIKE 'A%';

14 - SELECT [Link] + ' ' + [Link] AS FullName, [Link],


[Link]
FROM Employees e
JOIN Departments d ON [Link] = [Link]
JOIN Attendance a ON [Link] = [Link]
WHERE [Link] = '2023-10-24' AND [Link] > '08:30:00';

15 - SELECT COUNT(*) AS EmployeesCheckedInAfter830


FROM Attendance
WHERE WorkDate = '2023-10-24' AND CheckInTime > '08:30:00';

16 - SELECT SUM(BaseSalary) AS TotalBaseSalary, SUM(Bonus) AS TotalBonus


FROM Salaries;

17 - SELECT DISTINCT Location


FROM Departments;

You might also like