Docsity
Log inSign up
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

SQL Server Lab Assignment: Creating and Managing Databases and Tables, Assignments of Information Technology

Georgia Southern University–Armstrong CampusInformation Technology

Step-by-step instructions for university students to create and manage databases and tables using sql server management studio in sql server 2005. Creating a new database, creating a table, inserting values, and using select, update, and delete statements. Students will learn how to set primary keys, sort data, and update and delete records.

Typology: Assignments

Pre 2010

Uploaded on 08/04/2009

koofers-user-ycl-1
koofers-user-ycl-1 🇺🇸

10 documents

1 / 10

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
ITEC 4770-Client Server Systems Lab Assignment 1
Starting SQL Server
1. Launch SQL Server Management Studio (Start: All Programs: Microsoft SQL Server 2005: SQL Server
Management Studio)
2. Connect to the SQL server on the local machine.
3. Under the Object Explorer pane, expand the SQL Server database folder if it is not already expanded.
4. You will see something similar to the figure below:
Attaching a Database in SQL Server 2005
1. Right click on Databases and choose Attach…
2. Click on the Add… button
3. Navigate to the location of the database you want to attach: Northwind database is located by default at
C:\SQL Server 2000 Sample Databases\NORTHWND.MDF
Page 1 of 10
pf3
pf4
pf5
pf8
pf9
pfa

Partial preview of the text

Download SQL Server Lab Assignment: Creating and Managing Databases and Tables and more Assignments Information Technology in PDF only on Docsity!

ITEC 4770-Client Server Systems Lab Assignment 1

Starting SQL Server

  1. Launch SQL Server Management Studio (Start: All Programs: Microsoft SQL Server 2005: SQL Server Management Studio)
  2. Connect to the SQL server on the local machine.
  3. Under the Object Explorer pane, expand the SQL Server database folder if it is not already expanded.
  4. You will see something similar to the figure below: Attaching a Database in SQL Server 2005
  5. Right click on Databases and choose Attach…
  6. Click on the Add… button
  7. Navigate to the location of the database you want to attach: Northwind database is located by default at C:\SQL Server 2000 Sample Databases\NORTHWND.MDF

Creating a Database in SQL Server 2005

  1. Expand Databases.
  2. Select Databases and right click it.
  3. Choose New Database…
  4. The following screen is shown. Enter YourName (replace ITEC4770 with your actual name) as the name of the database.
  1. A new database ( YourName ) will appear under Databases on the left hand side. In this example, ITEC is shown as the last database in the Databases list.

Creating a table in the database

  1. Expand YourName. In this example we have expanded the ITEC4770 database.
  2. Right click on the Tables folder. Choose New Table…
  3. Create a table called “Employee” with the following fields. NOTE: we will name the table when we Save it. Column Name Datatype Allow Nulls employee_id int No last_name nvarchar(20) No first_name nvarchar(20) No middle_name nvarchar(20) Yes birth_date datetime Yes
  1. Click on Save.
  2. You will be asked to enter table name. Enter Employee and click OK.
  3. You have successfully created the Employee Table.

Insert values in the Employee table

  1. Select database YourName under the Object Explorer pane.
  2. Select the New Query option from the toolbar. A tab will be created for you to enter your query as shown below.
  3. Insert your first column in the Employee table. Type the following command: INSERT INTO Employee (employee_id, last_name, first_name, middle_name, birth_date) VALUES ('12', 'Smith', 'Mary', NULL, '02/02/1980')

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 (Due February 2): 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.