310 likes | 702 Views
Creating Tables. A Guide to SQL-- Chapter 3. COMMANDS…. CREATE TABLE DROP TABLE Use of NULL INSERT SELECT UPDATE DELETE. Table Creation. CREATE TABLE <tablename> (fieldname1 datatype1, fieldname2 datatype2,…, fieldnamen datatypen);. Rules:…. Data names:
E N D
Creating Tables A Guide to SQL-- Chapter 3
COMMANDS… • CREATE TABLE • DROP TABLE • Use of NULL • INSERT • SELECT • UPDATE • DELETE
Table Creation CREATE TABLE <tablename> (fieldname1 datatype1, fieldname2 datatype2,…, fieldnamen datatypen);
Rules:… • Data names: • Cannot exceed 30 characters • Must start with a letter • Can contain letters, numbers, and underscore(_) • Can’t contain spaces • Data types: • int, smallint, decimal(p,q), char(n), date, varchar(n) • See figure 3.11, page 71
Example… CREATE TABLE Rep (RepNum char(2) PRIMARY KEY, LastName char(15), FirstName char(15), Street char(15), City char(15), State char(2), Zip char(5), Commission decimal(7,2), Rate decimal(3,2));
DROP TABLE • Used to delete a table from a database • Syntax: • DROP TABLE <tablename>; • Example: • Delete the Rep table • Solution: • DROP TABLE Rep; • NOTE: WILL ALSO DELETE DATA!!!
Use of NULL VALUES • When you do or don’t want a field value to be left blank… • Example: CREATE TABLE Rep (Rep_Num Char (2) PRIMARY KEY, Last_Name Char (15) NOT NULL, …);
INSERT • INSERT clause – used to add data (in rows) to a table • Syntax: INSERT INTO <tablename> Values (value1, value2, value3, …, value n);
Example… • Add a new sales rep to the Rep table. • Number = 16, Name = Sharon Rands • Address = 826 Raymond, Altonville, FL 32543 • Commission = 0; Commission rate = 0.05 • Solution:
Note… • If some fields need to be left blank when using INSERT command: • Example: INSERT INTO Rep (RepNum, LastName, FirstName) VALUES (’85’, ‘Webb’, ‘Tina’);
SELECT – Used for data retrieval SELECT (fieldname1, fieldname2, fieldnamen) FROM <tablename> WHERE <condition>;
Example • List the number, name, and balance of all customers Select CustomerNum, CustomerName, Balance From Customer;
Example #2 • List all the information from the Part table Select * From Part;
Example #3 • List the name of every customer with a $10,000 credit limit Select CustomerName From Customer Where CreditLimit = 10000;
Example #4 • List the name of customer 148 Select CustomerName From Customer Where CustomerNum = ‘148’;
Your Turn • List the name of every customer living in the city of Grove. • Solution:
UPDATE • Used to make changes to existing data • Syntax: UPDATE <tablename> SET <fieldname> = value WHERE condition;
Example… • Change the street address of customer 524 to 1445 Rivard. • Solution:
DELETE • Syntax: DELETE FROM tablename WHERE condition; • Example: DELETE FROM Rep WHERE RepNum = ‘85’;