SQL SYNTAX

• SQL Create Database:
•  MySQL:
• CREATE DATABASE database_name;
• Oracle:
• In oracle database one can create the tables directly.
  
  
•  SQL Drop Database:
• MySQL:
• DROP DATABASE database_name;
  
  
• SQL Rename Database:
• MySQL:
• RENAME DATABASE old_db_name TO new_db_name;
• SQL server using T-SQL:
• ALTER DATABASE old_name MODIFY NAME = new_name;
•  SQL Select Database:
• MySQL:
• USE DATABASE database_name; 
• SQL Create Table:
• create table "tablename"  ("column1" "data type",  "column2" "data type",  "column3" "data type",  ...  "columnN" "data type");   
  
  NOTE: The datatype for MySQL is 'int' while for Oracle and SQLServer keyword used is 'number'.
•  Example:
  1. CREATE TABLE Employee (EmployeeID int,  FirstName varchar(255), LastName varchar(255),  Email varchar(255),  AddressLine varchar(255),  City varchar(255)  ); 
  
  
  
• Create a Table Using Another Table:
• CREATE TABLE table_name AS SELECT  column1 ,  column2 ,... FROM  old_table_name  WHERE ..... ;
  
  The following SQL creates a copy of the employee table:
•  CREATE TABLE EmployeeCopy AS
  SELECT EmployeeID, FirstName, Email
  FROM Employee;

•  SQL Primary Key with CREATE TABLE Statement:
• MySQL:
• CREATE TABLE Employee(
  EmployeeID NOT NULL,
  FirstName varchar(255) NOT NULL,
  LastName varchar(255),
  City varchar(255),
  PRIMARY KEY (EmployeeID)
  );  
• SQL Server / Oracle / MS Access:
• CREATE TABLE Employee(
  EmployeeID NOT NULL PRIMARY KEY,
  FirstName varchar(255) NOT NULL,
  LastName varchar(255),
  City varchar(255)
  );
• For MySQL / SQL Server /Oracle / MS Access (define a PRIMARY KEY constraints on multiple columns, and to allow naming of a PRIMARY KEY constraints. )
  • CREATE TABLE Employee(
    EmployeeID NOT NULL,
    FirstName varchar(255) NOT NULL,
    LastName varchar(255),
    City varchar(255), CONSTRAINT     PK_Employee PRIMARY KEY (EmployeeID, FirstName)
    ); 
• SQL Drop Table:
• DROP TABLE table_name;
• SQL Delete Table:
• DELETE FROM table_name [WHERE condition]; 
  
  
•  SQL Truncate Table:
• TRUNCATE TABLE table_name; 
• NOTE:
  When you drop a table:
  
• Table structure will be dropped
• Relationship will be dropped
• Integrity constraints will be dropped
• Access privileges will also be dropped
  On the other hand when we TRUNCATE a table, the table structure remains the same
•  NOTE:
  There is a slight difference b/w delete and truncate statement. The DELETE statement only deletes the rows from the table based on the condition defined by WHERE clause or delete all the rows from the table when condition is not specified. But it does not free the space containing by the table. The TRUNCATE statement: it is used to delete all the rows from the table and free the containing space.

• SQL Rename Table:
• ALTER TABLE table_name
  RENAME TO new_table_name;  
• RENAME old_table _name To new_table_name;
  

• SQL Copy Table:
• SELECT * INTO <destination_table> FROM <source_table> ;