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 | |
| 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.
