Assignment 4: Date: 06-01-2021
Brief History of SQL
Here, are important landmarks from the history of SQL:
- 1970 - Dr. Edgar F. "Ted" Codd described a relational model for databases.
- 1974 - Structured Query Language appeared.
- 1978 - IBM released a product called System/R.
- 1986 - IBM developed the prototype of a relational database, which is standardized by ANSI.
- 1989- First ever version launched of SQL
- 1999 - SQL 3 launched with features like triggers, object-orientation, etc.
- SQL2003- window functions, XML-related features, etc.
- SQL2006- Support for XML Query Language
- SQL2011-improved support for temporal databases
Here, are important landmarks from the history of SQL:
- 1970 - Dr. Edgar F. "Ted" Codd described a relational model for databases.
- 1974 - Structured Query Language appeared.
- 1978 - IBM released a product called System/R.
- 1986 - IBM developed the prototype of a relational database, which is standardized by ANSI.
- 1989- First ever version launched of SQL
- 1999 - SQL 3 launched with features like triggers, object-orientation, etc.
- SQL2003- window functions, XML-related features, etc.
- SQL2006- Support for XML Query Language
- SQL2011-improved support for temporal databases
Types of SQL
Here are five types of widely used SQL queries.
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language(DCL)
- Transaction Control Language(TCL)
- Data Query Language (DQL)
Here are five types of widely used SQL queries.
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language(DCL)
- Transaction Control Language(TCL)
- Data Query Language (DQL)
1)Create :To Create A Table
3) Alter : To Add New Columns,Modify column and to drop a column from a table.
4) Truncate: To remove all records from a table
5) Insert : To insert records into a table
6)Update : To change any value in a table
7)Delete :To remove records in a table
8)Grant : To give access permission any user
9)Revokle : To remove any user permission
10)Commit :To make changes to table permanently
11)Rollback To undue changes
12) Drop To Remove a table
13)Select To Retrive records froma table
1. DDL Commands - Data Definition
Language
Data Definition Language or DDL commands are used for changing
the structure of a table. In other words, DDL commands are capable of creating,
deleting, and modifying data.
All DDL commands are auto-committed which means that changes made by them are
automatically saved in the database. Following are the various DDL commands:
ALTER
Used for altering the structure of a database. Typically, the
ALTER command is used either to add a new attribute or modify the
characteristics of some existing attribute.
For adding new columns to the table:
General Syntax
ALTER TABLE table_name ADD (column_name1 data_type (size), column_name2 data_type (size),….., column_nameN data_type (size));Example
ALTER TABLE Student ADD (Address varchar2(20));ALTER TABLE Student ADD (Age number(2), Marks number(3));For modifying an existing column in the table:
General Syntax:
ALTER TABLE table_name MODIFY (column_name new_data_type(new_size));Example:
ALTER TABLE Student MODIFY (Name varchar2(20));The ALTER command can also be used for dropping a column from
the table:
General Syntax:
ALTER TABLE table_name DROP COLUMN column_name;Example:
ALTER TABLE Student DROP COLUMN Age;</pre.Note: - It is not possible to do the following using the ALTER command:·
- Change
the name of a column
- Change the name of a table
- Decrease the size of a column
CREATE
Used for creating a new table in the database. General Syntax:
CREATE TABLE table_name (column_name1 data_type(size), column_name2 data_type(size),…., column_nameN data_type(size));Example:
CREATE TABLE Employee(Name varchar2(20), D.O.B. date, Salary number(6);DROP
Used for deleting an entire table from the database and all the
data stored in it.
General Syntax:
DROP TABLE table_name;Example:
DROP TABLE Student;RENAME
Used for renaming a table.
General Syntax:
RENAME old_table_name TO new_table_nameExample:
RENAME Student TO Student_DetailsTRUNCATE
Used for deleting all rows from a table and free the space
containing the table.
General Syntax:
TRUNCATE TABLE table_name;Example:
TRUNCATE TABLE Student;2. DML Commands - Data Manipulation Language
The DML or Data Manipulation Language commands help in modifying
a relational database. These commands are not auto-committed, which simply
means that all changes made to the database using DML commands aren’t
automatically saved.
It is possible to rollback DML commands. Various DML commands
are:
DELETE
Used for removing one or more rows from a table.
General Syntax:
DELETE FROM table_name; (deletes all
rows from a table)
DELETE FROM table_name WHERE some_condition; (delete only
the row(s) where the condition is true)
Example:
DELETE FROM Student;DELETE FROM Student WHERE Name = “Akhil”;INSERT
Used for inserting data into the row of a table.
General Syntax:
INSERT INTO table_name (column_name1, column_name2,….,column_nameN) VALUES (value1, value2,….,valueN);ORINSERT INTO table_name VALUES (value1, value2,….,valueN);Example:
INSERT INTO Student (Name, Age) VALUES (“Vijay”, “25”);Insert command can also be used for inserting data into a table
from another table.
General Syntax:
INSERT INTO table_name1 SELECT column_name1, column_name2,….,column_nameN FROM table_name2;Example:
INSERT INTO Student SELECT Id, Stream FROM Student_Subject_DetailsUPDATE
Used to modify or update the value of a column in a table. It
can update all rows or some selective rows in the table.
General Syntax:
UPDATE table_name SET column_name1 = value1, column_name2 = value2,….,column_nameN = valueN (for updating all rows)UPDATE table_name SET column_name1 = value1, column_name2 = value2,….,column_nameN = valueN [WHERE CONDITION] (for updating particular rows)Example:
UPDATE Student SET Name = “Akhil” WHERE Id = 22;3. DCL Commands - Data Control Language
In order to protect the information in a table from unauthorized
access, DCL commands are used. A DCL command can either enable or disable a
user from accessing information from a database. List of user access privileges:
- ALTER
- DELETE
- INDEX
- INSERT
- SELECT
- UPDATE
GRANT
Used for granting user access privileges to a database.
General Syntax:
GRANT object_privileges ON table_name TO user_name1, user_name2,….,user_nameN;GRANT object_privileges ON table_name TO user_name1, user_name2,….,user_nameN WITH GRANT OPTION; (allows the grantee to grant user access privileges to others)Example:
GRANT SELECT, UPDATE ON Student TO Akhil BhadwalThis will allow the user to run only SELECT and UPDATE
operations on the Student table.
GRANT ALL ON Student TO Akhil Bhadwal WITH GRANT OPTIONAllows the user to run all commands on the table as well as
grant access privileges to other users.
REVOKE
Used for taking back permission given to a user.
General Syntax:
REVOKE object_privileges ON table_name FROM user1, user2,… userN;Example:
REVOKE UPDATE ON Student FROM Akhil;Note:
- A user who is not the owner of a table but has been given the privilege to grant
permissions to other users can also revoke permissions.
4. TCL Commands - Transaction Control Language
Transaction Control Language commands can only be used with DML
commands. As these operations are auto-committed in the database, they can’t be
used while creating or dropping tables. Various TCL commands are:
COMMIT
Used for saving all transactions made to a database. Ends the
current transaction and makes all changes permanent that were made during the
transaction. Releases all transaction locks acquired on tables.
General Syntax:
COMMIT;Example:
DELETE FROM Student WHERE Age = 25;COMMIT;ROLLBACK
Used to undo transactions that aren’t yet saved in the database.
Ends the transaction and undoes all changes made during the transaction.
Releases all transaction locks acquired on tables.
General Syntax:
ROLLBACK;Example:
DELETE FROM Student WHERE Age = 25;ROLLBACK;SAVEPOINT
Used for rolling back to a certain state known as the savepoint.
Savepoints need to be created first so that they can be used for rollbacking
transactions partially.
General Syntax:
SAVEPOINT savepoint_name;
Note:
- An active savepoint is one that has been specified since the last COMMIT or
ROLLBACK command.
5. DQL Commands - Data Query Language
DQL commands are used for fetching data from a relational
database. There is only one command, which is the SELECT command.
Equivalent to the projection
operation in relational algebra, SELECT command selects the
attribute based on the condition described by the WHERE clause.
General Syntax:
SELECT expressionsFROM table_nameWHERE condition1, condition2,…., conditionN;Example:
Suppose we have a relational table called Student that has all
the information regarding a student, such as name, roll no., stream, age,
address, etc. and we need to fetch data regarding all student names who are
less than 18 years of age, then we can use the SELECT command as follows:
SELECT student_nameFROM studentWHERE age < 18;The result will be a list of all the student names who are less
than 18 years of age.
The SELECT command can also be used for eliminating duplicates
from a table.
General Syntax:
SELECT DISTINCT column_name1, column_name2,…., column_nameN FROM table_name;Example:
SELECT DISTINCT Name, Age FROM Student;This command will scan through entire rows and will eliminate
rows that are identical.
Rows retrieved using the SELECT command can be sorted in either
ascending or descending order.
General Syntax:
SELECT column_name1, column_name2,…., column_nameN FROM table_name ORDER BY column_name; (gives results in ascending order)SELECT column_name1, column_name2,…., column_nameN FROM table_name ORDER BY column_name DESC; (gives results in descending order)Example:
SELECT Name, Age FROM Student ORDER BY Name;SELECT Name, Age FROM Student ORDER BY Name DES
=========================================
Assignment (All assignment send unitybca@gmail.com)05-01-21
CREATE TABLE EMPLOYEE
ENO,ENAME,SALARY,HRA,TA,DA,GSAL,TAX,NSAL.
INPUT ONLY ENO,ENAME,SALARY
TA - TRAVELLING ALLOWANCES
DA - DEARNESS ALLOWANCES
HRA - HOUSE RENT ALLOWANCES
GSAL - GROSS SALARY - SALARY+TA+DA+HRA
NSAL - NET SALARY
Calculate:
TA CALCULATE ON SALARY 10%
DA CALCULATE ON SALARY 15%
HRA CALCULATE ON SALARY 10%
CGSAL CALCULATE ON SALARY +TA+DA+HRA
TAX CALCULATE ON GSAL 30%
NSAL CALCULATE ON GSAL - TAX
CREATE TABLE EMPLOYEE
2 (ENO NUMBER(5) PRIMARY KEY,
3 ENAME CHAR(15),
4 SALARY NUMBER(5),
5 HRA NUMBER(5),
6 TA NUMBER(5),
7 DA NUMBER(5),
8 GSAL NUMBER(5),
9 TAX NUMBER(5),
10 NSAL NUMBER(5));
1 row created.
CALCULATE
SQL> DESC EMPLOYEE;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NOT NULL NUMBER(5)
ENAME CHAR(15)
SALARY NUMBER(5)
HRA NUMBER(5)
TA NUMBER(5)
DA NUMBER(5)
GSAL NUMBER(5)
TAX NUMBER(5)
NSAL NUMBER(5)
SQL> UPDATE EMPLOYEE SET HRA=SALARY*10/100;
5 rows updated.
SQL> UPDATE EMPLOYEE SET TA=SALARY*10/100;
5 rows updated.
SQL> UPDATE EMPLOYEE SET DA=SALARY*15/100;
5 rows updated.
SQL> UPDATE EMPLOYEE SET GSAL=SALARY+TA+DA+HRA;
Assignment 1(Dt.02-01-2021) by Sunday submit.
To unitybcom@gmail.com
Sub:-Name,RNo,subject,Class
Create Student Table :
SQL>CREATE TABLE STUDENT
(RNO NUMBER(2) PRIMARY KEY,
SNAME CHAR(15),
SUB1 NUMBER(2),
SUB2 NUMBER(2),
SUB3 NUMBER(2),
TOTAL NUMBER(3),
AVERAGE NUMBER(3));
Table Created
Insert 5 Records
SQL>INSERT INTO STUDENT VALUES(1,'HARIKA',67,89,99,0,0);
SQL>INSERT INTO STUDENT VALUES(2,'GEETHA',77,99,79,0,0);
Find Total Marks:
SQL>UPDATE STUDENT SET TOTAL=SUB1+SUB2+SUB3;
Find Total Average:
SQL>UPDATE STUDENT SET av AVERAGE=TOTAL/3;
To display all records:
SQL> SELECT * FROM STUDENT;
To display RNO,TOTAL,AVERAGE records:
SQL>SELECT RNO,TOTAL,AVERAGE FROM STDUENT;
To add Result Field:
SQL>ALTER TABLE STDUENT
ADD(RESULT CHAR(15));
To DELETE RNO 1:
SQL>DELETE STDUENT WHERE RNO=1;
To DELETE TABLE:
SQL>DROP TABLE STDUENT;