728x90 AdSpace

Latest Article

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





  • Blogger Comments
  • Facebook Comments

0 comments:

Post a Comment

Top