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
<span class="pln"> USE DB02TEST01</span><span class="pun">;</span><span class="pln"> </span>
Create Database Tables
Create a table Department
<span class="pln"> CREATE TABLE </span><span class="typ">Department_rumman_1637935</span><span class="pun">(</span><span class="pln"> </span><span class="typ">ProjectNo</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">ProjectName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="typ">Budget</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> </span><span class="kwd">null</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Department_rumman_1637935</span><span class="pln"> </span>
Insert some data inside the above table
<span class="pln"> INSERT INTO </span><span class="typ">Department_rumman_1637935</span><span class="pln"> </span><span class="pun">(</span><span class="typ">ProjectNo</span><span class="pun">,</span><span class="pln"> </span><span class="typ">ProjectName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Budget</span><span class="pun">)</span><span class="pln">VALUES </span><span class="pun">(</span><span class="str">'p1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'apollo'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">12000</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="str">'p2'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'gemeni'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">95000</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="str">'p3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'mercury'</span><span class="pun">,</span><span class="pln"> </span><span class="lit">185600</span><span class="pun">)</span><span class="pln"> </span>
Create a table project
<span class="pln"> CREATE TABLE </span><span class="typ">Project_rumman_1637935</span><span class="pun">(</span><span class="pln"> </span><span class="typ">DeptNo</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">DeptName</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="typ">Location</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">30</span><span class="pun">)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Project_rumman_1637935</span><span class="pln"> </span>
Insert Some data inside the above table
<span class="pln"> INSERT INTO </span><span class="typ">Project_rumman_1637935</span><span class="pln"> </span><span class="pun">(</span><span class="typ">DeptNo</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DeptName</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Location</span><span class="pun">)</span><span class="pln">VALUES </span><span class="pun">(</span><span class="str">'d1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Research'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Dallas'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="str">'d2'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Accounting'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Settle'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="str">'d3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Marketing'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Dallas'</span><span class="pun">)</span><span class="pln"> </span>
Create a table Employee
<span class="pln"> CREATE TABLE </span><span class="typ">Employee_rumman_1637935</span><span class="pun">(</span><span class="pln"> empNo integer</span><span class="pun">,</span><span class="pln"> empFname varchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> empLname varchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> </span><span class="typ">DeptNo</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> </span><span class="pun">)</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> </span>
Insert Some data in the above table
<span class="pln"> INSERT INTO </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> </span><span class="pun">(</span><span class="pln">empNo</span><span class="pun">,</span><span class="pln"> empFname</span><span class="pun">,</span><span class="pln"> empLname</span><span class="pun">,</span><span class="pln"> </span><span class="typ">DeptNo</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">25348</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Mathew'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Smith'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'d3'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">10102</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ann'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Jones'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'d3'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">18316</span><span class="pun">,</span><span class="pln"> </span><span class="str">'John'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Bamimore'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'d1'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">29346</span><span class="pun">,</span><span class="pln"> </span><span class="str">'James'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'James'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'d2'</span><span class="pun">)</span><span class="pln"> </span>
Create a table works on
<span class="pln"> CREATE TABLE </span><span class="typ">WorksOn_rumman_1637935</span><span class="pun">(</span><span class="pln"> emoNo integer</span><span class="pun">,</span><span class="pln"> projectNo varchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">Job</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> </span><span class="typ">EnterDate</span><span class="pln"> date </span><span class="pun">)</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> </span>
Insert some data inside the above table
<span class="pln"> INSERT INTO </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> </span><span class="pun">(</span><span class="pln">empNo</span><span class="pun">,</span><span class="pln"> projectNo</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Job</span><span class="pun">,</span><span class="pln"> </span><span class="typ">EnterDate</span><span class="pun">)</span><span class="pln"> VALUES </span><span class="pun">(</span><span class="lit">10102</span><span class="pun">,</span><span class="pln"> </span><span class="str">'p1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Analyst'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1997.10.01'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">10102</span><span class="pun">,</span><span class="pln"> </span><span class="str">'p3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Manager'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1999.1.1'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">25348</span><span class="pun">,</span><span class="pln"> </span><span class="str">'p3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Clerk'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1998.1.15'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">18316</span><span class="pun">,</span><span class="pln"> </span><span class="str">'p2'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'NULL'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1998.6.1'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">29346</span><span class="pun">,</span><span class="pln"> </span><span class="str">'p2'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'NULL'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1997.12.15'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">2581</span><span class="pun">,</span><span class="pln"> </span><span class="str">'p3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Analyst'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1998.10.15'</span><span class="pun">),</span><span class="pln"> </span><span class="pun">(</span><span class="lit">9031</span><span class="pun">,</span><span class="pln"> </span><span class="str">'p1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Manager'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1998.4.15'</span><span class="pun">)</span><span class="pln"> </span>
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.
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> </span>
Get the employee numbers for all clerks
<span class="pln"> SELECT empNo FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> WHERE </span><span class="typ">Job</span><span class="pln"> </span><span class="pun">=</span><span class="str">'Clerk'</span><span class="pln"> </span>
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.
<span class="pln"> SELECT empNo FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> WHERE projectNo </span><span class="pun">=</span><span class="pln"> </span><span class="str">'p2'</span><span class="pln"> AND empNo </span><span class="pun"><</span><span class="pln"> </span><span class="lit">10000</span><span class="pln"> </span>
Get the employee numbers for all employees who didn’t enter their project in 1998.
<span class="pln"> CREATE PROCEDURE </span><span class="typ">ProjNot1998</span><span class="pln"> AS </span><span class="typ">Begin</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> SELECT empNo</span><span class="pun">,</span><span class="typ">EnterDate</span><span class="pln"> FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> WHERE </span><span class="typ">EnterDate</span><span class="pln"> NOT LIKE </span><span class="str">'1998%'</span><span class="pln"> </span><span class="kwd">end</span><span class="pln"> EXEC </span><span class="typ">ProjNot1998</span><span class="pln"> DROP PROCEDURE </span><span class="typ">ProjNot1998</span><span class="pln"> </span>
Get the employee numbers for all employees who have a leading job( i.e., Analyst or Manager) in project p1
<span class="pln"> SELECT empNo FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> WHERE projectNo </span><span class="pun">=</span><span class="pln"> </span><span class="str">'p1'</span><span class="pln"> AND job IN</span><span class="pun">(</span><span class="str">'Analyst'</span><span class="pun">,</span><span class="str">'Manager'</span><span class="pun">)</span><span class="pln"> </span>
Get the enter dates for all employess in project p2 whose jobs have not been determined yet.
<span class="pln"> SELECT </span><span class="typ">EnterDate</span><span class="pln"> FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> WHERE projectNo</span><span class="pun">=</span><span class="str">'p2'</span><span class="pln"> AND </span><span class="typ">Job</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'NULL'</span><span class="pln"> SELECT </span><span class="typ">EnterDate</span><span class="pln"> FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> WHERE projectNo</span><span class="pun">=</span><span class="str">'p2'</span><span class="pln"> AND </span><span class="typ">Job</span><span class="pln"> IS NULL </span>
Get the employee numbers and last names of all employees whose first names contain two letter t’s.
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> WHERE empFname </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Mathew'</span><span class="pln"> UPDATE </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> SET empFname </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Matthew'</span><span class="pln"> WHERE empNo </span><span class="pun">=</span><span class="pln"> </span><span class="str">'25348'</span><span class="pln"> SELECT empNo</span><span class="pun">,</span><span class="pln">empLname FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> WHERE empFname LIKE </span><span class="str">'%t%t%'</span><span class="pun">;</span><span class="pln"> </span>
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.
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> SELECT empNo</span><span class="pun">,</span><span class="pln">empFname FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> WHERE empLname LIKE </span><span class="str">'_[a,o]%es'</span><span class="pun">;</span><span class="pln"> </span>
Get the employee numbers of all employees whose departments are located in Seattle.
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Project_rumman_1637935</span><span class="pln"> WHERE </span><span class="typ">Location</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Settle'</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Project_rumman_1637935</span><span class="pln"> WHERE </span><span class="typ">DeptNo</span><span class="pln"> </span><span class="pun">=(</span><span class="pln">SELECT </span><span class="typ">DeptNo</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Project_rumman_1637935</span><span class="pln"> WHERE </span><span class="typ">Location</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'Settle'</span><span class="pun">)</span><span class="pln"> </span>
Find the last and first names of all employess who entered their projects on 04.01.1998
<span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> SELECT empNo</span><span class="pun">,</span><span class="pln">empFname FROM </span><span class="typ">Employee_rumman_1637935</span><span class="pln"> WHERE empNo </span><span class="pun">=(</span><span class="pln">SELECT empNo FROM </span><span class="typ">WorksOn_rumman_1637935</span><span class="pln"> WHERE </span><span class="typ">EnterDate</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'04.01.1998'</span><span class="pun">)</span><span class="pln"> </span>
