COM111

ghhhhh

COM 111 LECTURE SCHEDULE 17

COM 111 LECTURE SCHEDULE 17

Data Definition Language, Data Manipulation Language

DDL(DATA DEFINITON LANGUAGE) commands

DDL consists of those commands in SQL that directly create or delete database objects such as tables. The most important DDL commands are:

i.                    CREATE TABLE: To create a new table

ii.                  DROP TABLE: To delete a table

CREATE TABLE:

SQL CREATE TABLE Syntax

CREATE TABLE table_name
( column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
.... );

• The column_name parameters specify the names of the columns of the table.
• The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).
• The size parameter specifies the maximum length of the column of the table.

SQL CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City.

We use the following CREATE TABLE statement:

CREATE TABLE Persons
(
PersonID int, LastName char(255),
FirstName varchar(255), Address char(255),
City char(255)
);

• The PersonID column is of type int and will hold an integer.
• The LastName, FirstName, Address, and City columns are of type char and will hold characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

PersonID	LastName	FirstName	Address	City

SQL DROP COMMAND:

 The syntax is:

DROP TABLE table-name;

Example is:

DROP TABLE Persons;

DML (DATA MANIPULATION LANGUAGE) Commands

The most important DML commands are:

i.                    INSERT: To insert data into a table.

ii.                  DELETE:  To delete data from a table.

iii.                SELECT: To retrieve a data from a table.

INSERT COMMAND:

SQL INSERT Syntax

• It is possible to write the INSERT INTO statement in two forms.
• The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name VALUES (value1,value2,value3,...);

INSERT INTO Example

To insert a new row in the "Customers" table.

We can use the following SQL statement:

 

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

The selection from the "Customers" table will now look like this:

Customer ID	Customer Name	Contact Name	Address	City	Postal   Code	Country
87	Indira	Pirkko Koskitalo	Torikatu 38	Cbe	641 003	India
88	Abinaya	Paula Parente	Rua do Mercado, 12	Resende	08737-363	Brazil
89	Shree	Karl Jablonski	305 - 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Harini	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Ben	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland

SELECT COMMAND:

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT * FROM table_name;

Example:

SELECT * FROM customers;

 

The SQL DELETE Statement:

The DELETE statement is used to delete rows in a table. It is possible to delete all rows in a table without deleting the table.

DELETE * FROM table_name;

Example:

DELETE * FROM customers;

COM 111 LECTURE SCHEDULE 16

COM 111 LECTURE SCHEDULE 16

Data Definition Language, Data Manipulation Language

DDL(DATA DEFINITON LANGUAGE) commands

DDL consists of those commands in SQL that directly create or delete database objects such as tables. The most important DDL commands are:

i.                    CREATE TABLE: To create a new table

ii.                  DROP TABLE: To delete a table

CREATE TABLE:

SQL CREATE TABLE Syntax

CREATE TABLE table_name
( column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
.... );

• The column_name parameters specify the names of the columns of the table.
• The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).
• The size parameter specifies the maximum length of the column of the table.

SQL CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City.

We use the following CREATE TABLE statement:

CREATE TABLE Persons
(
PersonID int, LastName char(255),
FirstName varchar(255), Address char(255),
City char(255)
);

• The PersonID column is of type int and will hold an integer.
• The LastName, FirstName, Address, and City columns are of type char and will hold characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

PersonID	LastName	FirstName	Address	City

SQL DROP COMMAND:

 The syntax is:

DROP TABLE table-name;

Example is:

DROP TABLE Persons;

DML (DATA MANIPULATION LANGUAGE) Commands

The most important DML commands are:

i.                    INSERT: To insert data into a table.

ii.                  DELETE:  To delete data from a table.

iii.                SELECT: To retrieve a data from a table.

INSERT COMMAND:

SQL INSERT Syntax

• It is possible to write the INSERT INTO statement in two forms.
• The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name VALUES (value1,value2,value3,...);

INSERT INTO Example

To insert a new row in the "Customers" table.

We can use the following SQL statement:

 

Example

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');

The selection from the "Customers" table will now look like this:

Customer ID	Customer Name	Contact Name	Address	City	Postal   Code	Country
87	Indira	Pirkko Koskitalo	Torikatu 38	Cbe	641 003	India
88	Abinaya	Paula Parente	Rua do Mercado, 12	Resende	08737-363	Brazil
89	Shree	Karl Jablonski	305 - 14th Ave. S. Suite 3B	Seattle	98128	USA
90	Harini	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
91	Ben	Zbyszek	ul. Filtrowa 68	Walla	01-012	Poland

SELECT COMMAND:

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

The result is stored in a result table, called the result-set.

SQL SELECT Syntax

SELECT * FROM table_name;

Example:

SELECT * FROM customers;

 

The SQL DELETE Statement:

The DELETE statement is used to delete rows in a table. It is possible to delete all rows in a table without deleting the table.

DELETE * FROM table_name;

Example:

DELETE * FROM customers;