SQL Statement Example with Lab assignment

Using Select statement in SQL

The basic syntax for select statement is:

SELECT column_list FROM table_name

·          Select all rows and all columns:

SELECT * FROM Employee

·          Select limited columns from a table:

SELECT employee_id, last_name, first_name FROM Employee

·          Select specific information:

SELECT * FROM Employee WHERE last_name = 'Smith'

·          Select information sorted in a particular way:

SELECT * FROM Employee ORDER BY employee_id

·          Select information sorted in descending order

SELECT * FROM Employee ORDER BY employee_id DESC

Using Update statement in SQL

The basic syntax for update statement is:

UPDATE tablename SET columnname = ‘somevalue’ WHERE criteria

·          UPDATE Employee SET last_name = ‘Marburger’ WHERE first_name = ‘Mary’

Using Delete statement in SQL

The basic syntax for DELETE statement is:

DELETE FROM tablename WHERE criteria

·          DELETE FROM Employee WHERE last_name = ‘Marburger’

Lab Assignment

Execute the following steps, and after each step take a screen shot that shows you executed the step correctly. For instance, for step one you will show a snapshot of Object Explorer with Tables/dbo.Department/Columns expanded. Insert all images into a document and hand it over to you professor before class, on the due date.

1.             Create a table called Department. The table has following columns:

dept_id – Primary key

dept_name

dept_head

dept_loc

Select the best datatypes for all the columns.

2.             Insert the following values in the Department table

dept_id	dept_name	dept_head	dept_loc
IT	Information Technology	Kam Lau	Gamble Hall
CS	Computer Science	Ashraf Saad	Science Center
ES	Engineering Studies	Tom Murphy	Victor Hall
IE	International Education	James Anderson	Gamble Hall

Select

3.             Select all columns of Department table.

4.             Select all columns sorted by dept_name.

5.             Select all columns but only for rows that are located in Gamble Hall.

6.             Update the department name to Information Systems whose department identification is IT.

7.             Delete a row where the department is International Education.