Using Data Manipulation Language

Instructor: Dr. Frank Chao, Robert Clinton, Amy Huang, David Ball, Walter Rotenberry Page 1 of 3
Lab 07 – Using Data Manipulation Language
Objectives:
♦ Be familiar with the concept of Data Manipulation
♦ Use SELECT, INSERT, UPDATE, and DELETE statements
Goals:
♦ Learn how to manipulate data in database tables
Grading:
♦ Student needs to show instructor the answers to each hands-on exercise in order to get full credit
♦ If you are an online student, you need to put all the SQL coding in a text file (e.g. lab07.txt) and send it to your instructor.
___________________________________________________________________________________________
Login information:
Students need to initiate a SQL Server session in SQL Server Management Studio via SQL Server Authentication.
User ID: scott Password: tiger123$ Database: HR
___________________________________________________________________________________________
1. You first need to build a table for the lab. Please type the following in your query window.
a. Write down the steps in SSMS on how to show the table structure.
b. How many columns does myemp table have? What are their names?
[Answer:]
Instructor: Dr. Frank Chao, Robert Clinton, Amy Huang, David Ball, Walter Rotenberry Page 2 of 3
2. Using INSERT statements.
a. Add the values into myemp table as shown below. You do not need to specify the column names.
b. Write down a query to show the employees whose salaries are higher than 4000.
Id
First_name
Last_name
Salary
101
Charles
Knowles
6000
102
Cole
Church
4500
103
Samir
Kak
3000
104
Cindy
Johnson
5000
105
Sandy
Brown
3200
[Answer:]
a.
b.
3. Using UPDATE statements.
Ms. Cindy Johnson married with Mr. Charles Knowles. Hence, you need to change Cindy’s last name from Johnson into Knowles. Another good thing is that Cindy’s salary is raised by 20%. Write down a query to reflect the change on Cindy’s record based on Cindy’s employee id.
Your result display should be like this:
[Answer:]
Instructor: Dr. Frank Chao, Robert Clinton, Amy Huang, David Ball, Walter Rotenberry Page 3 of 3
4. Using DELETE statement.
Unfortunately, Mr. Cole Church is not happy with his salary. He found a better job and quit. As a data manager, you need to remove Mr. Church from myemp table. Write down a query to reflect this change based on his employee id.
[Answer:]
5. All of the above is completely fictitious, as you know. Now, you no longer need the table myemp. Write a statement on how to remove the table data as well as the table structure.
[Answer:]