CSC239_Lab02-1 (4)

Middlesex County College CSC239-60 Computer Science Department Version 5 Lab Assignment Database Concepts Page 1 __________________________________________________________________________________________ Lab #2– Creating And Updating Tables A. Overview 1. This lab will introduce you to creating tables updating them by adding, changing, and deleting table content. 2. You will enter data into the data base table and then display the contents. 3. The SQL activities will then be screen printed and pasted into a word document, saved, and printed. B. SQL Notation: 1. SQL CREATE TABLE Statement: a. Used to create a table in a database. b. Tables are organized into rows and columns; and each table must have a name. c. SQL CREATE TABLE Syntax CREATE TABLE table_name ( column_name1 data_type ( size ), column_name2 data_type ( size ), column_name3 data_type ( size ), .... ); d. Explanation: (1) The column_name parameters specify the names of the columns of the table. (2) The data_type parameter specifies what type of data the column can hold (for example: varchar, integer, decimal, date, etc.). (3) The size parameter specifies the maximum length of the column of the table. e. Example: (1) To create a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City. (2) Example CREATE TABLE statement: CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) ); (3) Explanation: (a) The PersonID column is of type int and will hold an integer. (b) The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters

Middlesex County College CSC239-60 Computer Science Department Version 5 Lab Assignment Database Concepts Page 2 __________________________________________________________________________________________ 2. SQL INSERT INTO Statement a. Used to insert new records in a table. b. SQL INSERT INTO Syntax: (1) It is possible to write the INSERT INTO statement in two different formats. (2) The first format does not specify the column names where the data will be inserted, only their values. (a) Syntax: INSERT INTO table_name VALUES ( value1 , value2 , value3 ,...); (b) Example: INSERT INTO Persons VALUES (20,'Bob', 'Brown','123 Smith Street', 'Edison'); (3) The second format specifies both the column names and the values to be inserted. (a) Syntax: INSERT INTO table_name ( column1 , column2 , column3 ,...) VALUES ( value1 , value2 , value3 ,...); (b) Example: INSERT INTO Persons (CustomerName, ContactName, Address, City, State, Zip, Country) VALUES (20,’John’,’Smith’,’123 Miller Street’, 'Edison’); 3. SQL INSERT For Data Only in Specified Columns a. Used to insert data only in specific columns. b. SQL INSERT INTO Syntax: (1) INSERT INTO table_name ( column1 , column2 , column3 ,...) VALUES ( value1 , value2 , value3 ,...); c. Example explanation: (1) The following SQL statement will insert a new row, but only insert data in the "CustomerName", "City", and "Country" columns: (2) Example INSERT INTO Persons (PersonID, FirstName, LastName, Address, City) VALUES (30,'John','Smith','123 Miller Street', 'Edison'); 4. SQL UPDATE Statement: a. Used to update existing records in a table. b. SQL UPDATE Syntax: UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value; c. Example: (1) To update the customer "Alan Alda" with a new contact person and city. (2) Use the following SQL statement: UPDATE Persons SET FirstName='Johny' WHERE FirstName = 'John'; d. Update Warning: (1) Be careful when updating records. (2) If the WHERE clause would have been omitted, many records could have been accidentally updated.