CREATING A DATABASE
To create a database use the following syntax CREATE DATABASE db_name;
Example: CREATE DATABASE Company_DB_2;
DELETING A DATABASE
To delete an existing database use the syntax: DROP DATABASE db_name;
Example: DROP DATABASE Company_DB_2;
CREATING A TABLE
First, select the appropriate database from the available databases and then execute the query. The following SQL query will create a table named Employee.
CREATE TABLE Employee (
FName char(255) NOT NULL,
Minit char(255),
LName char(255),
SSN varchar (255) NOT NULL,
BDate date NOT NULL,
Address varchar(255) NOT NULL,
Sex char (50) NOT NULL,
Salary numeric(10,4) NOT NULL,
SuperSSN varchar (255),
Dept_Number int,
PRIMARY KEY (SSN)
);
INSERTING INTO A TABLE
SET DATEFORMAT dmy;
insert into EMPLOYEE (Fname, Minit, Lname, ssn, Bdate, address, superssn,
Dept_Number, salary, sex)
VALUES('srinivas','n','patil','SRI001','31.07.1983','Kormangala, Bangalore','SSN001',1,60500,'Male');
UPDATING A TABLE
update Employee
set salary=75000
where ssn='SRI001';
DELETE A RECORD FROM A TABLE
DELETE FROM EMPLOYEE WHERE ssn='SRI001';
VIEWING ALL DATABASES
SELECT * FROM sys.databases;
VIEWING ALL TABLES IN A DATABASE
To view all tables present in a database we can use the syntax: SELECT * FROM DatabaseName.INFORMATION_SCHEMA.TABLES;
SELECT * FROM Company_DB_2.INFORMATION_SCHEMA.TABLES;
SAVING AND ROLLBACK
BEGIN TRANSACTION
BEGIN TRY
insert into EMPLOYEE (Fname, Minit, Lname, ssn, Bdate, address, superssn,
Dept_Number, salary, sex)
VALUES('Srinidhi','M','Sharma','SRI002','21.07.1992','Jaipur, Rajasthan','RJ001',1,65500,'Female');
insert into EMPLOYEE (Fname, Minit, Lname, ssn, Bdate, address, superssn,
Dept_Number, salary, sex)
VALUES('Vikas','S','Dubey','VIK003','15.02.1979','Lucknow, UP','UP001',1,175500,'Male');
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION;
END CATCH
The following code will generate an exception which will be caught at the CATCH block and prints the occurred exception (Violation of PRIMARY KEY constraint ‘PK__Employee__CA1E8E3D07F6335A’. Cannot insert duplicate key in object ‘dbo.Employee’. The duplicate key value is (VIK003).) And the data will not be inserted into the table.
BEGIN TRANSACTION
BEGIN TRY
insert into EMPLOYEE (Fname, Minit, Lname, ssn, Bdate, address, superssn,
Dept_Number, salary, sex)
VALUES('Vikas','S','Dubey','VIK003','15.02.1979','Lucknow, UP','UP001',1,175500,'Male');
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION;
END CATCH
SQL Queries to perform the following operations:
Rename the database, Alter a Table, Back-up the database, Rename/ Truncate / Drop Tables, and Restore the database from the backup file.
RENAME
Renaming the database
Syntax: ALTER DATABASE Current_Database_Name MODIFY NAME = New_Name;
ALTER DATABASE Company_DB_2 MODIFY NAME = SRIJNC2018;
ALTER
Alter a table by adding a column to the existing table
Syntax: ALTER TABLE Table_name ADD column_name datatype;
ALTER TABLE Dependents ADD address varchar (255);
ALTER TABLE Dependents ADD email char (255);
Alter a table by changing the data type of a column in an existing table
Syntax: ALTER TABLE Table_name ALTER COLUMN column_name datatype;
ALTER TABLE Dependents ALTER COLUMN email varchar (255);
Alter a table by dropping a column in an existing table
Syntax: ALTER TABLE Table_name DROP COLUMN column_name;
ALTER TABLE Dependents DROP COLUMN address;
Renaming a Tables
Syntax: EXEC sp_rename 'Current_Table_Name', 'New_Table_Name';
EXEC sp_rename 'DEPENDANT', 'DEPENDENT';
BACKUP
Creating a Back-Up of the database we created
Syntax: BACKUP DATABASE databasename TO DISK = 'filepath';
BACKUP DATABASE Company_DB TO DISK = 'E:\My_Database\Company_DB.bak';
Truncating contents of a table
Syntax: TRUNCATE TABLE Table_Name;
TRUNCATE TABLE employee;
Dropping a Tables
Syntax: DROP TABLE Table_Name;
DROP TABLE employee;
DROP Database
Syntax: DROP DATABASE db_name;
DROP DATABASE Company_DB;
RESTORE
Restoring the database from the backup file
Syntax: Restore database Database_name from disk = 'path_of_the_backup_file' with replace;
Restore database Company_DB from disk = 'E:\My_Database\Company_DB.bak' with replace;
For a given set of relation schemes, create tables and perform the following Simple Queries, Simple Queries with Aggregate functions, and Queries with Aggregate functions (groupby and having clause).
Simple Query with Aggregate Function: The following simple SQL query will get the minimum, maximum, and average salary from the employee table.
SELECT MIN(salary) AS min_salary FROM employee;
SELECT MAX(salary) AS max_salary FROM employee;
SELECT AVG(salary) AS average_salary FROM employee;
The following simple SQL query will get the minimum, maximum, and average salary from the employee table belonging to Accounts department.
SELECT
MAX(Salary) AS MaxSalary,
MIN(Salary) AS MinSalary,
AVG(Salary) AS AvgSalary
FROM
Employee
WHERE
Department_Name = 'Accounts';
Query with Aggregate Function and GROUP BY Clause: The following SQL query will calculate the total salary of each department (Example: Accounts, Marketing and Sales, Research Department) and display the final amount.
SELECT Department_name, SUM(salary) AS total_sales
FROM Employee
GROUP BY Department_name;
Query with Aggregate Function, GROUP BY, and HAVING Clause: The following SQL query first create a separate group according to the department name, then calculate the average salary of each department and then display only the department average salary which is more than 150000.
SELECT Department_name, AVG(salary) AS avg_sales
FROM Employee
GROUP BY Department_name
HAVING AVG(salary) > 150000;
Query with Multiple Aggregate Functions: We can find the values for multiple aggregate functions in one query and the following is an example of that where we are calculating minimum, maximum, and average salary in each department.
SELECT Department_name, MIN(salary) AS min_salary, MAX(salary) AS max_salary, AVG(salary) AS avg_salary
FROM employee
GROUP BY Department_name;
Execute the following queries
a. How the resulting salaries if every employee working on the ‘Research Department’ is given a 10% raise.
b. Find the sum of the salaries of all employees of the ‘Accounts’ department, as well as the maximum salary, the minimum salary, and the average salary in this department
Adding a column to the existing table
Syntax: ALTER TABLE Table_name ADD New_Column_Name Data_type;
ALTER TABLE employee ADD Department_name varchar (255);
Adding values to the newly added column
Syntax:
update Table_Name
set Column_Name=Value
where Column_Name=Value ;
update Employee
set Department_name='Research Department'
where DNUMBER=1 ;
update Employee
set Department_name='Accounts'
where DNUMBER=2 ;
update Employee
set Department_name='Marketing and Sales'
where DNUMBER=3 ;
select * from EMPLOYEE;
Increasing the salaries of all employees by 10% who are working in the ‘Research Department’.
UPDATE Employee
SET Salary = Salary * 1.10
WHERE department_name = 'Research Department';
select * from EMPLOYEE;
Find the sum of the salaries of all employees of the ‘Accounts’ department
SELECT SUM(Salary) AS TotalSalary
FROM Employee
WHERE Department_Name = 'Accounts';
select * from EMPLOYEE;
Find the maximum salary, the minimum salary, and the average salary in the Accounts department.
SELECT
MAX(Salary) AS MaxSalary,
MIN(Salary) AS MinSalary,
AVG(Salary) AS AvgSalary
FROM
Employee
WHERE
Department_Name = 'Accounts';
Execute the following queries
a. Retrieve the name of each employee Controlled by Department number n (use EXISTS operator).
b. Retrieve the name of each dept and the number of employees working in each department that has at least 2 employees.
Retrieve the name of each employee Controlled by Department number n (use EXISTS operator).
SELECT Fname, Mname, LNAME, SSN, DNumber
FROM employee
WHERE EXISTS (
SELECT 1
FROM department
WHERE employee.DNumber = department.DNumber
AND department.DNumber = 2
);
In SQL, SELECT 1 is a common way to write a subquery that returns a constant value of 1 for each row in the result set. It’s often used with the EXISTS clause or in other situations where you want to check for the existence of something without needing to retrieve actual data.
Retrieve the name of each dept and the number of employees working in each department that has at least 2 employees.
SELECT Department_name, COUNT(*) AS employee_count FROM employee
GROUP BY department_name HAVING COUNT(*) >= 2;
Execute the following queries
a. For each project, retrieve the project number, the project name, and the number of employees who work on that project using GROUP BY
b. Retrieve the name of employees who were born in the year 1990’s
SELECT
PNumber,PName, count(employee.SSN)as No_Employees
FROM
PROJECT, employee
WHERE
EMPLOYEE.DNUMBER=Project.DNUMBER
GROUP BY PNAME, PNUMBER
SELECT
FNAME
FROM
employee
WHERE
YEAR(BDATE) BETWEEN 1990 AND 1999;
For each Department that has more than ‘n’ employees, retrieve the department number and number of employees who are making a salary of more than 40000.
SELECT
DNUMBER,
COUNT(*) AS num_employees_over_40000
FROM
employee
WHERE
salary > 40000
GROUP BY
DNUMBER
HAVING
COUNT(*) > 2;
For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project.
SELECT
PNumber,PName, count(employee.SSN)as No_Employees
FROM
PROJECT, employee
WHERE
EMPLOYEE.DNUMBER=Project.DNUMBER
GROUP BY PNAME, PNUMBER
HAVING
COUNT(*) > 2;
For a given set of relation tables perform the following:
Creating Views (with and without check option), Dropping views, Selecting from a view
Creating a View without Check option
Syntax:
create view view_name as select * from table_name
The following SQL command will create a view employee_view with columns the same as the original table EMPLOYEE.
create view employee_view as select * from EMPLOYEE
Creating a View with Check option
Syntax for creating a view with different column names than the original table columns:
create view view_name as select * from table_name
create view view_name (column_1, column_2, column_3, ...) as select Original_column_1_name, Original_column_2_name, Original_column_3_name, . . . from Original_table_name where condition with check option;
The following SQL command will create a view with the columns ProjName, ProjNo, ProjLocation representing the columns PNAME, PNUMBER, PLOCATION in the original table when the condition DNUMBER=2 satisfies.
create view Education_view (ProjName, ProjNo, ProjLocation) as select PNAME, PNUMBER, PLOCATION from PROJECT where DNUMBER=2 with check option;
Syntax for creating view with same column names as in the original table
CREATE VIEW view_name AS
SELECT column_1, column_2, column_3, . . .
FROM table_name
WHERE condition
WITH CHECK OPTION;
The following SQL command will create a view with the columns DNAME, DNUMBER, MGRSSN from the original table when the condition MGRSTARTDATE > ’01-01-2021′ is satisfied.
CREATE VIEW Department_view AS
SELECT DNAME, DNUMBER, MGRSSN
FROM DEPARTMENT
WHERE MGRSTARTDATE > '01-01-2021'
WITH CHECK OPTION;
Insert, Update, Delete view
Any modification (insert, update, delete) done to the original table or in the view, the changes will reflect in both the view and the original table. (NOTE: Make sure that you add the primary key column to the view you are creating)
INSERT
set dateformat dmy;
insert into EMPLOYEE_view (Fname, Mname, Lname, ssn, Bdate, address, superssn,
DNumber, salary, sex)
VALUES('Richie','','Ridhi','RICH01','31.07.2006','Kormangala, Bangalore','RICH001',1,260500,'Female');
Select * from employee
Select * from employee_view
UPDATE
update EMPLOYEE_view set salary=350000 where ssn='RICH01'
DELETE
delete from EMPLOYEE where ssn='RICH01'
RENAME and DROP a VIEW
In SQL, we can’t directly rename a view using a simple RENAME statement like we can with tables. To rename a view, we typically need to use a combination of two statements: CREATE and DROP. That is, first create a new view using the existing view and then delete the earlier created view.
CREATE VIEW Project_View AS select ProjName, ProjNo, ProjLocation FROM education_view;
Drop view education_view