SQL Languages
• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
• Data Retrieval Language (DRL) / Data Query Language (DQL)
Data Definition Language
DDL statements are used to build and modify the structure of your tables and other objects in the database. When you execute a DDL statement, it takes effect immediately.
• Create
• Alter
• Drop
• Truncate
• Rename (Oracle 9i)
1. Create :
It is used to create database objects like Tables, Views, Sequences, Indexes.
Syntax:
Creating a Table
CREATE TABLE TABLE_NAME (colname 1 datatype(size), colname 2 datatype(size) …..);
Example:
CREATE TABLE EMPLOYEE
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
Table naming Rules:
Table names and column names:
• Must begin with a letter.
• Must be 1–30 characters long.
• Must contain only A–Z, a–z, 0–9, _, $, and #.
• Must not duplicate the name of another object owned by the same user.
• Must not be an Oracle server reserved word.
2. Alter :
• It is used to change structure of the existing table.
• After you create a table, you may need to change the table structure because: you omitted a column, your column definition needs to be changed, or you need to remove columns. You can do this by using the ALTER TABLE statement.
Use the ALTER TABLE statement to:
- Add a new column.
- Modify an existing column.
- Define a default value for the new column.
- Drop a column.
Guidelines when Modifying the Columns :
• You can increase the width or precision of a numeric column.
• You can increase the width of numeric or character columns.
• You can decrease the width of a column only if the column contains only null values or if the table has no rows.
• You can change the data type only if the column contains null values.
• You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values.
• A change to the default value of a column affects only subsequent insertions to the table
Examples:
• ADD column: It is used to add number of columns into the existing tables
Syntax: Alter table tablename add (colname1 datatype(size), colname2 datatype(size)…..);
Example: -- Adding two new columns to the EMPLOYEE table
ALTER TABLE EMPLOYEE
ADD (BONUS NUMBER(7, 2),
ALLOWANCE NUMBER(7, 2));
• Modify : It is used to change column datatypes or datatype size only.
Syntax: Alter table tablename modify(colname1 datatype(size), colname2 datatype(size)…);
-- Modifying the data types of SAL and COMM columns in the EMPLOYEE table
ALTER TABLE EMPLOYEE
MODIFY (SAL NUMBER(10, 2),
COMM NUMBER(10, 2));
• Alter….Drop: It is used to remove columns from the existing table.
Syntax: Alter table tablename drop column columnname;
(or) To remove single or multiple columns with using parenthesis “()”
Syntax: Alter table tablename drop (colname1, colname2, …);
Example: -- Dropping the BONUS and ALLOWANCE columns from the EMPLOYEE table
ALTER TABLE EMPLOYEE
DROP COLUMN BONUS,
DROP COLUMN ALLOWANCE;
3. Drop :
Syntax:
DROP TABLE table_name;
In the syntax:
table_name is the name of the table
• All data and structure in the table is deleted.
• All indexes are dropped.
• You cannot roll back the DROP TABLE statement.
• Any views and synonyms remain but are invalid.
• Any pending transactions are committed.
• Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a table.
Example: Drop Table EMPLOYEE;
4. Truncate :
Syntax:
TRUNCATE TABLE table_name;
In the syntax:
table_name is the name of the table
The TRUNCATE TABLE statement:
– Removes all rows from a table
– Releases the storage space used by that table
– You cannot roll back row removal when using TRUNCATE.
Example: Truncate Table EMPLOYEE;
5. Rename : It is used to rename a table and renaming a column also.
Renaming a Table:
Syntax: Rename oldtablename to newtablename;
Example: Rename Employee to Customers
Renaming a Column:
Syntax: Alter table table_name rename column oldname to newname;
Example: Alter table employee rename column manager to employee;
• Data manipulation language (DML) is a core part of SQL.
• A DML statement is executed when you:
– Add new rows to a table
– Modify existing rows in a table
– Remove existing rows from a table
• A transaction consists of a collection of DML statements that form a logical unit of work.
a) INSERT
b) UPDATE
c) DELETE
These commands are used to manipulate data into a table.
a) Insert :
Add new rows to a table by using the INSERT statement.
Syntax :
INSERT INTO TABLENAME VALUES (value1, value2,….);
Example : Inserting a new record into the EMPLOYEE table
INSERT INTO EMPLOYEE VALUES (101, 'John Doe', 'Manager'……);
In the syntax:
This statement with the VALUES clause adds only one row at a time to a table.
Syntax :
INSERT INTO TABLENAME (colname1, colname2,..) values (value1, value2, …);
This syntax is used to skip the columns.
• Insert a new row containing values for each column.
• List values in the default order of the columns in the table.
• Optionally, list the columns in the INSERT clause.
Example :
INSERT INTO EMPLOYEE VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),800, NULL, 20);
Syntax :
UPDATE tablename
SET columnname = newvalue [, column = value, ...]
[WHERE condition];
In the syntax:
Tablename is the name of the table
Column is the name of the column in the table to populate
Value is the corresponding value or subquery for the column
Condition identifies the rows to be updated and is composed of
column names expressions, constants, subqueries, and
comparison operators
Example:
UPDATE EMPLOYEE
SET empno = 1001
WHERE empno = 30 ;
• Modify existing rows with the UPDATE statement.
• Update more than one row at a time, if required.
• Specific row or rows are modified if you specify the WHERE clause.
• All rows in the table are modified if you omit the WHERE clause.
• We can update multiple columns in the SET clause of an UPDATE statement by writing multiple subqueries.
c) Delete :
We can remove existing rows from a table by using the DELETE statement.
SYNTAX:
DELETE [FROM] table [WHERE condition];
In the syntax:
Table is the table name
Condition identifies the rows to be deleted and is composed of column names, expressions, constants, sub-queries, and comparison operators
- Specific rows are deleted if you specify the WHERE clause.
- All rows in the table are deleted if you omit the WHERE clause.
Example for Delete Statement
DELETE FROM EMPLOYEE;
DELETE FROM EMPLOYEE WHERE empno = 1001;
DCL (Data Control Language)
DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.
List of DCL commands:
a) GRANT: This command gives users access privileges to the database.
Syntax:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
b) REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.
Syntax:
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
Transaction Control Language
Before COMMIT or ROLLBACK
Every data change made during the transaction is temporary until the transaction is committed.
State of the data before COMMIT or ROLLBACK statements are issued:
• Data manipulation operations primarily affect the database buffer; therefore, the previous state of the data can be recovered.
• The current user can review the results of the data manipulation operations by querying the tables.
• Other users cannot view the results of the data manipulation operations made by the current user. The Oracle server institutes read consistency to ensure that each user sees data as it existed at the last commit.
• The affected rows are locked; other users cannot change the data in the affected rows.
SAVEPOINT :
• SAVEPOINT command is used for saving all the current point in the processing of a transaction.
• It marks and saves the current point in the processing of a transaction.
Syntax:
SAVEPOINT <savepoint_name>
Example:
SAVEPOINT no_update;
It is used to temporarily save a transaction, so that you can roll back to that point whenever necessary.
State of the Data after COMMIT
Make all pending changes permanent by using the COMMIT statement.
Following a COMMIT statement:
• Data changes are written to the database.
• The previous state of the data is permanently lost.
• All users can view the results of the transaction.
The locks on the affected rows are released; the rows are now available for other users to perform new data changes.
For Example: DELETE
FROM EMPLOYEE
WHERE EMP_ID = 100 ;
---1 row deleted.
SELECT *
FROM EMPLOYEE
WHERE EMP_ID = 100;
--- No rows selected.
COMMIT;
---- Commit complete.
State of the Data After ROLLBACK
Discard all pending changes by using the ROLLBACK statement.
Following a ROLLBACK statement:
- Data changes are undone.
- The previous state of the data is restored.
- The locks on the affected rows are released.
DELETE FROM EMPLOYEE;
-- 25,000 rows deleted.
SELECT * FROM EMPLOYEE;
-- No rows selected
ROLLBACK;
-- Rollback complete.
SELECT * FROM EMPLOYEE;
-- 25,000 rows selected
Data Retrieval Language
SELECT
In all relational databases we can retrieve data from table using select statement
Basic SELECT Statement
Syntax:
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table;
In the syntax:
SELECT is a list of one or more columns
* selects all columns
DISTINCT suppresses duplicates
Column|expression selects the named column or the expression
alias gives selected columns different headings
FROM table specifies the table containing the columns
Example: select columnname1,columnname2,…
from tablename
where condition;
Writing SQL Statement
• SQL statements are not case sensitive, unless indicated.
• SQL statements can be entered on one or many lines.
• Keywords cannot be split across lines or abbreviated.
• Clauses are usually placed on separate lines for readability and ease of editing.
• Indents should be used to make code more readable.
• Keywords typically are entered in uppercase; all other words, such as table names and columns, are entered in lowercase.
SELECT Clause
The SELECT clause is mandatory. It specifies a list of columns to be retrieved from the tables in the FROM clause. It has the following general format:
SELECT colname1, colname2,.. From table_name;
The column names in the select list can be qualified by the appropriate table name:
SELECT p.columnname1, p.columnname2 FROM table_name p;
A column in the select list can be renamed by following the column name with the new name. For example:
SELECT name supplier, city location FROM s;
No comments:
Post a Comment