create table employee - createtableemployee(firstvarchar(15...

This preview shows page 1 - 3 out of 24 pages.

create table employee (first varchar(15),  last varchar(20),  age number(3),  address varchar(30),  city varchar(20),  state varchar(20)); To create a new table, enter the keywords create table followed by the table name, followed by an open parenthesis, followed by the first column name, followed by the data type for that column, followed by any optional constraints, and followed by a closing parenthesis. It is important to make sure you use an open parenthesis before the beginning table, and a closing parenthesis after the end of the last column definition. Make sure you seperate each column definition with a comma. All SQL statements should end with a ";". The table and column names must start with a letter and can be followed by letters, numbers, or underscores - not to exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for tables or column names (such as "select", "create", "insert", etc). Data types specify what the type of data can be for that particular column. If a column called "Last_Name", is to be used to hold names, then that particular column should have a "varchar" (variable-length character) data type. Here are the most common Data types: char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. varchar(size) Variable-length character string. Max size is specified in parenthesis. number(size) Number value with a max number of column digits specified in parenthesis. date Date value number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal. What are constraints? When tables are created, it is common for one or more columns to have constraints associated with them. A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, a "unique" constraint specifies that no two records can have the same value in a particular column. They must all be unique. The other two most popular constraints are "not null" which specifies that a column can't be left blank, and "primary key". A "primary key" constraint defines a unique identification of each record (or row) in a table.
Image of page 1

Subscribe to view the full document.

CREATE TABLE table_name ( column_name1 data_type ( size ), column_name2 data_type ( size ), column_name3 data_type ( size ), .... ); CREATE TABLE Persons ( PersonID int, LastName varchar( 255 ), FirstName varchar( 255 ), Address varchar( 255 ), City varchar( 255 ) ); INSERT INTO table_name VALUES ( value1 , value2 , value3 ,...); The second form specifies both the column names and the values to be inserted: INSERT INTO table_name ( column1 , column2 , column3 ,...) VALUES ( value1 , value2 , value3 ,...); INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ( 'Cardinal' , 'Tom B. Erichsen' , 'Skagen 21' , 'Stavanger' , '4006' , 'Norway' ); To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name
Image of page 2
Image of page 3
  • Fall '16
  • Jerrine Roderique
  • Calculus, Foreign key, primary key, Candidate key

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern