Introduction to SQL

• Query 


  A specific request for data manipulation issued by the end-user or the application to the DBMS.
• Stands for Structured Query Language
• Pronounced as S-Q-L or “sequel”
• SQL First Command


  Create database and table structures.
• SQL Second Command


  Perform various types of data manipulation and data administration.
• SQL Third Command 


  Query the database to extract useful information.
• Popular Database Management Tools

  • Microsoft SQL Server
  • MySQL
  • Oracle RDBMS
  • Microsoft Access
• Exact numeric

  • bigint,
  • bit,
  • decimal,
  • int,
  • money,
  • numeric
• Approximate numeric

  • float,
  • real
• Date and time

  • date,
  • datetime,
  • time
• Character strings

  • char,
  • text,
  • varchar
• Unicode character strings

  • nchar,
  • ntext,
  • nvarchar
• Binary strings

  • binary,
  • image,
  • varbinary
• Other data types

  • cursor,
  • sql_variant,
  • table,
  • uniqueidentifier,
  • xml
• Arithmetic

  • +
  • -
  • *
  • /
  • %
• Comparison

  =

  >

  <

  >=

  <=

  <>
• Compound

  • +=
  • -=
  • *=
  • /=
  • %=
• Logical

  AND

  OR

  NOT

  LIKE

  IN

  BETWEEN

  EXISTS

  ANY

  ALL
• SQL Data Definition Commands

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE TABLE
  • DROP TABLE – TRUNCATE TABLE
  • ALTER TABLE
• CREATE DATABASE 


  Creates a new database

  Syntax: CREATE DATABASE database_name
• DROP DATABASE 


  Deletes an existing database

  Syntax: DROP DATABASE database_name
• CREATE TABLE 


  Creates a new table in a database

  Syntax: CREATE TABLE table_name (column1 datatype, …);

  Example: CREATE TABLE Students (StudentID varchar(11), LastName varchar(99), FirstName varchar(99), Section varchar(5));
• DROP TABLE 


  Deletes an existing table in a database

  Syntax: DROP TABLE table_name;
• TRUNCATE TABLE


  To delete only the table's data

  Syntax: TRUNCATE TABLE table_name;
• ALTER TABLE 


  Adds, deletes, or modifies columns in an existing table

  Syntax to add: ALTER TABLE table_name ADD column datatype;

  Syntax to delete: ALTER TABLE table_name DROP COLUMN column;

  Syntax to modify: ALTER TABLE table_name ALTER COLUMN column datatype;
• NOT NULL on CREATE TABLE 


  Ensures that a column cannot have a NULL value upon creating a table Example: CREATE TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99));
• NOT NULL on ALTER TABLE 


  Ensures that a column in an existing table cannot have a NULL value

  Example: ALTER TABLE table_name ALTER COLUMN column varchar(5) NOT NULL;
• UNIQUE on CREATE TABLE 


  Ensures that all values in a column are different upon creating a table

  Example: CREATE TABLE Students (StudentID varchar(11) NOT NULL UNIQUE, LastName varchar(99) NOT NULL);
• UNIQUE on ALTER TABLE 


  Creates a UNIQUE constraint on a column of an existing table.

  Syntax: ALTER TABLE table_name ADD UNIQUE (column);
• PRIMARY KEY on CREATE TABLE 


  Uniquely identifies each row in a table.

  Example: CREATE TABLE Students (StudentID varchar(11) NOT NULL PRIMARY KEY, LastName varchar(99) NOT NULL);
• PRIMARY KEY on ALTER TABLE 


  Creates a PRIMARY KEY constraint on a column of an existing table.

  Syntax: ALTER TABLE table_name ADD PRIMARY KEY (column);
• FOREIGN KEY on CREATE TABLE 


  Uniquely identifies a row in another table

  Example: CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, TableNumber int NOT NULL, CustomerID int FOREIGN KEY REFERENCES Customers (CustomerID));
• FOREIGN KEY on ALTER TABLE 


  Creates a FOREIGN KEY constraint on a column of an existing table.

  Syntax: ALTER TABLE table1_name ADD FOREIGN KEY (table1_column) REFERENCES table2_name (table2_column);
• CHECK on CREATE TABLE


  Ensures that all values in a column satisfy a specific condition upon creating a table

  Example: CREATE TABLE Students (StudentID varchar(11) NOT NULL, LastName varchar(99) NOT NULL, FirstName varchar(99) NOT NULL, Age int CHECK (Age>=15));
• CHECK on ALTER TABLE


  Ensures that all values in a column of an existing table satisfy a specific condition

  Syntax: ALTER TABLE table_name ADD CHECK (condition);
• DEFAULT on CREATE TABLE


  Sets a default value for a column when there is no value specified

  EXAMPLE: column datatype DEFAULT 'word'
• DEFAULT on ALTER TABLE


  Sets a default value for a column of an existing table when there is no value specified

  Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT 'value' FOR column;
• The basic data manipulation commands are:

  • INSERT
  • SELECT
  • UPDATE
  • DELETE
• INSERT INTO 


  Adds new rows/records to a table

  Syntax: INSERT INTO table_name (columns) VALUES (values);
• Syntax: INSERT INTO table_name VALUES (values);To add new records to all the columns of a table