Insert, Select, Update and Delete Operations in SQL Server (DML Operations)

In this section we will discuss about the Insert, Select, Update and Delete Operations in SQL Server (DML Operations)

Use a Database

USE DB02TEST01;

Create Database Tables

Create a table Department

CREATE TABLE Department_rumman_1637935(
ProjectNo varchar(10),
ProjectName varchar(20),
Budget int null
)

SELECT * FROM Department_rumman_1637935

Insert some data inside the above table

INSERT INTO Department_rumman_1637935 (ProjectNo, ProjectName, Budget)VALUES
('p1', 'apollo', 12000),
('p2', 'gemeni', 95000),
('p3', 'mercury', 185600)

Create a table project

CREATE TABLE Project_rumman_1637935(
DeptNo varchar(10),
DeptName varchar(20),
Location varchar(30)
)


SELECT * FROM Project_rumman_1637935

Insert Some data inside the above table

INSERT INTO Project_rumman_1637935 (DeptNo, DeptName, Location)VALUES
('d1', 'Research', 'Dallas'),
('d2', 'Accounting', 'Settle'),
('d3', 'Marketing', 'Dallas')

Create a table Employee

CREATE TABLE Employee_rumman_1637935(
empNo integer,
empFname varchar(20),
empLname varchar(20),
DeptNo varchar(10)
)


SELECT * FROM Employee_rumman_1637935

Insert Some data in the above table

INSERT INTO Employee_rumman_1637935 (empNo, empFname, empLname, DeptNo) VALUES
(25348, 'Mathew', 'Smith', 'd3'),
(10102, 'Ann', 'Jones', 'd3'),
(18316, 'John', 'Bamimore', 'd1'),
(29346, 'James', 'James', 'd2')

Create a table works on

CREATE TABLE WorksOn_rumman_1637935(
emoNo integer,
projectNo varchar(10),
Job varchar(10),
EnterDate date
)


SELECT * FROM WorksOn_rumman_1637935

Insert some data inside the above table

INSERT INTO WorksOn_rumman_1637935 (empNo, projectNo, Job, EnterDate) VALUES
(10102, 'p1', 'Analyst', '1997.10.01'),
(10102, 'p3', 'Manager', '1999.1.1'),
(25348, 'p3', 'Clerk', '1998.1.15'),
(18316, 'p2', 'NULL', '1998.6.1'),
(29346, 'p2', 'NULL', '1997.12.15'),
(2581, 'p3', 'Analyst', '1998.10.15'),
(9031, 'p1', 'Manager', '1998.4.15')

The tables Department, Employee, Project and Works_on tables will be used for the Exercises in this session.

Question and Solutions

Get all row of the works_on table.

 SELECT * FROM WorksOn_rumman_1637935

Get the employee numbers for all clerks

 SELECT empNo FROM WorksOn_rumman_1637935 WHERE Job ='Clerk'

Get the employee numbers for employees working in project p2, and having employee numbers smaller than 10000. Solve this problem with two different but equivalent SELECT statements.

 SELECT empNo FROM WorksOn_rumman_1637935 WHERE projectNo = 'p2' AND empNo < 10000

Get the employee numbers for all employees who didn’t enter their project in 1998.

 CREATE PROCEDURE ProjNot1998
AS
Begin
SELECT * FROM WorksOn_rumman_1637935
SELECT empNo,EnterDate FROM WorksOn_rumman_1637935 WHERE EnterDate NOT LIKE '1998%'
end

EXEC ProjNot1998

DROP PROCEDURE ProjNot1998

Get the employee numbers for all employees who have a leading job( i.e., Analyst or Manager) in project p1

 SELECT empNo FROM WorksOn_rumman_1637935 WHERE projectNo = 'p1' AND job
IN('Analyst','Manager')

Get the enter dates for all employess in project p2 whose jobs have not been determined yet.

 SELECT EnterDate FROM WorksOn_rumman_1637935 WHERE projectNo='p2' AND Job = 'NULL'

SELECT EnterDate FROM WorksOn_rumman_1637935 WHERE projectNo='p2' AND Job IS NULL

Get the employee numbers and last names of all employees whose first names contain two letter t’s.

 SELECT * FROM Employee_rumman_1637935

SELECT * FROM Employee_rumman_1637935 WHERE empFname = 'Mathew'

UPDATE Employee_rumman_1637935 SET empFname = 'Matthew' WHERE empNo = '25348'

SELECT empNo,empLname FROM Employee_rumman_1637935 WHERE empFname LIKE '%t%t%';

Get the employee numbers and first names of all employees whose last names have a letter o or a as the second character and end with the letters es.

 SELECT * FROM Employee_rumman_1637935
SELECT empNo,empFname FROM Employee_rumman_1637935 WHERE empLname LIKE '_[a,o]%es';

Get the employee numbers of all employees whose departments are located in Seattle.

 SELECT * FROM Project_rumman_1637935 WHERE Location = 'Settle'
SELECT * FROM Project_rumman_1637935 WHERE DeptNo =(SELECT DeptNo from
Project_rumman_1637935 WHERE Location = 'Settle')

Find the last and first names of all employess who entered their projects on 04.01.1998

 SELECT * FROM Employee_rumman_1637935
SELECT empNo,empFname FROM Employee_rumman_1637935 WHERE
empNo =(SELECT empNo FROM WorksOn_rumman_1637935 WHERE EnterDate = '04.01.1998')