1 / 20

Creating Tables

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:

harsha
Télécharger la présentation

Creating Tables

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating Tables A Guide to SQL-- Chapter 3

  2. COMMANDS… • CREATE TABLE • DROP TABLE • Use of NULL • INSERT • SELECT • UPDATE • DELETE

  3. Table Creation CREATE TABLE <tablename> (fieldname1 datatype1, fieldname2 datatype2,…, fieldnamen datatypen);

  4. 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

  5. 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));

  6. 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!!!

  7. 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, …);

  8. INSERT • INSERT clause – used to add data (in rows) to a table • Syntax: INSERT INTO <tablename> Values (value1, value2, value3, …, value n);

  9. 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:

  10. Note… • If some fields need to be left blank when using INSERT command: • Example: INSERT INTO Rep (RepNum, LastName, FirstName) VALUES (’85’, ‘Webb’, ‘Tina’);

  11. SELECT – Used for data retrieval SELECT (fieldname1, fieldname2, fieldnamen) FROM <tablename> WHERE <condition>;

  12. Example • List the number, name, and balance of all customers Select CustomerNum, CustomerName, Balance From Customer;

  13. Example #2 • List all the information from the Part table Select * From Part;

  14. Example #3 • List the name of every customer with a $10,000 credit limit Select CustomerName From Customer Where CreditLimit = 10000;

  15. Comparison Operators

  16. Example #4 • List the name of customer 148 Select CustomerName From Customer Where CustomerNum = ‘148’;

  17. Your Turn • List the name of every customer living in the city of Grove. • Solution:

  18. UPDATE • Used to make changes to existing data • Syntax: UPDATE <tablename> SET <fieldname> = value WHERE condition;

  19. Example… • Change the street address of customer 524 to 1445 Rivard. • Solution:

  20. DELETE • Syntax: DELETE FROM tablename WHERE condition; • Example: DELETE FROM Rep WHERE RepNum = ‘85’;

More Related