Create table Student - Lecture notes 2 PDF

Preview

Summary

Table creation...

Description

Create a Table:

Create table Student( Stud_ID Integer PRIMARY KEY, Stud_LastName as Last Name char(30), Stud_FirstName char(30), Stud_StreetAddress char(30), Stud_City Char(30), Stud_State char(2)); --------------------------------------------------------------------------------------------------Alter a Table: Alter table Student Add Stud_Street char(30), Stud_City Char(30); -------------------------------------------------------------------------------------------Insert Data into Table INSERT INTO Student (Stud_ID, Stud_LastName, Stud_FirstName, Stud_StreetAddress, Stud_State, Stud_City) VALUES (10,'Banerjee','Dharitri', '8804 Delfield Lane', 'VA', 'Fairfax' );

INSERT INTO Student (Stud_ID, Stud_LastName, Stud_FirstName) VALUES (16,'Duert','Joann'); Insert into Student VALUES (15,'Smith', 'John', '8804 Delfield Lane', 'VA', 'Fairfax' ); ------------------------------------------------------------------------------------------

update student set Stud_state = 'WA' where Stud_ID = 10; update student set Stud_state = 'CA';

update student set Stud_City = 'Vienna ', Stud_State = 'VA', Stud_Street = 'Chain Bridge Raod' where Stud_ID = 10;

INSERT INTO Student (Stud_ID, Stud_LastName, Stud_FirstName, Stud_StreetAddress, Stud_State, Stud_City) VALUES (11,'Banerjee','Gage', '2804 Chain Bridge', 'VA', 'Vienna' );

Reading Specified Columns and Specified Rows from a Single Table You can combine the techniques just shown to select some columns and some rows from a table. For example, to obtain only the FirstName, LastName, Phone, and Department values of employees in the accounting department, you use: /* *** SQL-QUERY-CH03-09 *** */ SELECT FirstName, LastName, Phone, Department FROM EMPLOYEE WHERE Department = 'Accounting';

You can combine two or more conditions in the WHERE clause by using the AND keyword and the OR keyword. As stated previously, if the AND keyword is used, only rows meeting all the conditions will be selected. However, if the OR keyword is used, then rows that meet any of the conditions will be selected. For example, the following query uses the AND keyword to ask for employees that work in accounting and have the phone number 360-285-8310: /* *** SQL-QUERY-CH03-10 *** */ SELECT FirstName, LastName, Phone, Department FROM EMPLOYEE WHERE Department = 'Accounting' AND Phone = '360-285-8310';

However, the following query uses the OR keyword to ask for employees that work in accounting or have the phone number 360-285-8410: /* *** SQL-QUERY-CH03-11 *** */ SELECT FirstName, LastName, Phone, Department FROM EMPLOYEE WHERE Department = 'Accounting' OR Phone = '360-285-8410';...