Page 1 :
CHAPTER 14, SQL Commands, SQL is Structured Query Language, which is a database language for storing, manipulating and, retrieving data stored in relational database., Features of SQL:, ✓ SQL is an ANSI and ISO standard computer language for creating and manipulating, database., ✓ SQL allows the user to create, update, delete and retrieve data from a database., ✓ SQL is very simple and easy to learn., ✓ All relational database management systems like MySQL, MS Access, Oracle, Sybase and, SQL Server use SQL as standard database language., SQL ARCHITECTURE:, , There are various components included in the process. These components are Query Dispatcher,, Optimization Engines, Classic Query Engine and SQL Query Engine, etc., SQL Commands:, The commands in SQL can be classified into four groups based on their nature:, 1. DDL - Data Definition Language:, These commands are used to define database schema, alter the database schema and drop the, database schema., Some of the DDL commands are CREATE, ALTER, DROP etc., 2. DML - Data Manipulation Language:, These commands are used for storing, retrieving and updating the data in the database., Some of the DML commands are INSERT, DELETE, UPDATE, SELECT etc., 3. DCL - Data Control Language:, These commands are used for providing security to database object., Some of the DCL commands are GRANT & REVOKE, 4. TCL – Transaction Control Language, These commands are used to control the transaction in a database., Some of the TCL commands are COMMIT, ROLLBACK etc., Department of Computer science, , 1, , M.V.KIRANKUMAR

Page 2 :
Data types in SQL:, Data type indicates the kind of value the field will contain., The following are the most common data types of SQL., ➢ NUMBER:, It is used to store a numeric value in a field column. It may be integer or floting value., Ex:, Marks NUMBER (3), Percentage NUMBER (3,2), ➢ CHAR:, It is used to store character type data in a column., Ex:, Name CHAR (8), ➢ VARCHAR / VARCHAR2:, It is used to store variable length alpha numeric data., Ex: Address VARCHAR2 (30), ➢ DATE:, It is used to store Dates in columns., Ex: DOB DATE, ➢ TIME:, It is used to store Times in columns., Ex: College TIME, ➢ LONG:, It is used to store variable length up to 2GB size., Ex: Description LONG, Operator in SQL:, There are 3 different types of SQL operators. They are, i. Arithmetic Operators, ii. Comparison Operators or Relational Operators, iii. Logical Operators, Arithmetic Operators:, , Department of Computer science, , Operator, , Name, , Example, , +, , Addition, , A+B, , -, , Subtraction, , A-B, , *, , Multiplication, , A*B, , /, , Division, , A/B, , %, , Modulus, , A%B, , 2, , M.V.KIRANKUMAR

Page 3 :
Comparison Operators or Relational Operators:, Operator, , Name, , Example, , ==, , Equal to, , A==B, , !=, , Not Equal to, , A != B, , >, , Greater than, , A>B, , <, , Lesser than, , A<B, , >=, <=, , Greater than or, Equal to, Lesser than or, Equal to, , A>=B, A<=B, , Logical Operators:, ➢ AND:, The AND operator allows the existence of multiple conditions in an SQL statement's, WHERE clause., ➢ OR:, The OR operator is used to combine multiple condition s in an SQL statement's WHERE, clause., ➢ BETWEEN:, The BETWEEN operator is used to search for values that are within set of values, given the, minimum value and the maximum value., ➢ IN:, The IN operator is used to compare a value to a list of literal values that have been specified., ➢ LIKE:, The LIKE operator is used to compare a value to similar values using Wild card operators., ➢ NOT:, The NOT operator reverses the meaning of the logical operator with which it is used., , SQL Constraints:, Constraints are the rules enforced on data columns on table. These are used to limit the type of data, that can go into a table., Some of the commonly used constraints are:, Primary Key, Foreign Key, , Not Null, Unique, , Default, , ➢ Primary Key Constraints:, This constraint defines a column or combination of columns which uniquely identifies each, row in the table., ➢ Foreign Key Constraints:, This constraint identifies any column referencing the PRIMARY KEY in another table., Department of Computer science, , 3, , M.V.KIRANKUMAR

Page 4 :
➢ Not Null Constraint :, This constraint ensures all rows in the table contain a definite value for the column which is, specified as not null., ➢ Unique Key Constraint :, This constraint ensures that a column or a group of columns in each row have a distinct value., ➢ Default Constraint :, This constraints provide a default value to a column when insert command does not provide, specific value., DDL Commands:, ➢ CREATE Command:, This command is used to create a new Table., Syntax:, Example:, CREATE TABLE Table_name, (, ColumnName1 data_type,, ColumnName2 data_type,, ., ., ., ColumnNameN data_type, );, , CREATE TABLE Student, (, RegNo NUMBER(5),, Name VARCHAR2 (10),, Marks NUMBER(3), );, , ➢ ALTER TABLE Command:, This command is used to Add a Column, Delete a Column and Modify the data type of, column in a table., ❖ To Add a New Column, we use following syntax, Syntax:, ALTER TABLE Tablename ADD ( ColumnName1 Datatype, ColumnName2 Datatype,………);, Ex:, , ALTER TABLE Student ADD ( Result VARCHAR2(5));, , ❖ To Alter existing data type of column, we use following syntax, Syntax:, ALTER TABLE Tablename MODIFY ( ColumnName1 Datatype, ColumnName2 Datatype,……);, Ex:, , ALTER TABLE Student MODIFY ( Result VARCHAR2(10));, , ❖ To Delete a column in a table, we use following syntax, Syntax:, ALTER TABLE Tablename DROP ( ColumnName1, ColumnName2,……);, Ex:, , ALTER TABLE Student DROP ( Result );, , ➢ DROP TABLE Command:, This command is used to Delete the Existing Table from the database., Syntax:, DROP TABLE Tablename;, Ex:, DROP TABLE Student;, Department of Computer science, , 4, , M.V.KIRANKUMAR

Page 5 :
DML Commands:, ➢ INSERT Command:, This command is used to Insert Data into a database or table., Syntax:, INSERT INTO Tablename VALUES (Value1, Value2, ………ValueN);, Ex:, INSERT INTO Student VALUES ( ‘101’, ‘Kiran’, 86);, ➢ UPDATE Command:, This command is used to change the data in a database or table., Syntax:, UPDATE Tablename SET Columnname = Value [WHERE Condition];, Ex:, UPDATE Student SET Total = Sub1 + Sub2;, UPDATE Student SET Result = ‘PASS’ WHERE RegNo=145;, ➢ DELETE Command:, This command is used to Delete row or rows from the table., Syntax:, DELETE FROM Tablename [WHERE Condition];, Ex:, DELETE FROM Student;, DELETE FROM Student WHERE Result=’FAIL’;, ➢ SELECT Command:, This command is used to retrieve or display data from the database., Syntax:, SELECT ColumnList FROM Tablename [ WHERE Condition] [ HAVING, Condition] [GROUP BY] [ORDER BY ] );, EX:, SELECT RegNo FROM Student;, SELECT RegNo, Name FROM Student;, SELECT * FROM Student;, SELECT * FROM Student WHERE Marks>=35;, SELECT * FROM Student ORDER BY Name;, SELECT * FROM Student ORDER BY Name DESC;, , ORDER BY:, ORDER BY clause is used to sort the data in ascending or descending order, based on one or, more columns., Syntax:, SELECT Column-list FROM Table_name ORDER BY Columnname ASC / DESC;, Ex:, SELECT * FROM Student ORDER BY Name;, SELECT * FROM Student ORDER BY Name DESC;, , Department of Computer science, , 5, , M.V.KIRANKUMAR

Page 6 :
GROUP BY:, GROUP BY clause is used in collaboration with the SELECT statement to arrange identical, data into groups., Syntax:, SELECT Column-list FROM Table_name GROUP BY Columnname ;, Ex:, SELECT * FROM Student GROUP BY Result;, DISTINCT:, DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the, duplicate records and gives only unique records., Syntax:, SELECT DISTINCT Columnname FROM Table_name [WHERE Condition];, Ex:, SELECT DISTINCT Name FROM Student;, JOINS:, Joins clause is used to combine records from two or more tables in a database., There are different types of joins available in SQL:, INNER JOIN, LEFT JOIN, FULL JOIN, SELF JOIN, CARTESIAN JOIN, NULL:, It is used to find NULL values from the table., Ex:, , SELECT * FROM Student WHERE Marks IS NULL., , Creating Views:, Database views are created using the CREATE VIEW statement. Views can be created from, a single table or multiple tables., Syntax:, CREATE VIEW view_name AS SELECT column1, column2.. FROM table_name WHERE [condition];, , Ex:, CREATE VIEW Stud AS SELECT Reg.No, Name FROM Student;, , DCL commands:, ➢ GRANT Command:, This command is used to give permission to the user., ➢ REVOKE Command::, This command is used to takes back permission granted from user., TCL Commands:, ➢ COMMIT:, This command makes all the changes made by statements issued permanent., ➢ ROLL BACK:, This command undoes all changes since the beginning of transaction., Department of Computer science, , 6, , M.V.KIRANKUMAR

Page 7 :
SQL built-in functions:, There are two types of functions in SQL., 1 Single Row Functions or Scalar Functions, 2 Group Functions:, 1, , Single Row Functions or Scalar Functions:, There are four types of single row functions. They are:, , ❖ Numeric Functions: These are functions that accept numeric input and return numeric values., Ex: ABS ( ), FLOOR ( ), ROUND ( ), etc, ❖ Character or Text Functions: These are functions that accept character input and can return both, character and number values., Ex: LOWER ( ), UPPER ( ), LENGTH ( ) etc, ❖ Date Functions: These functions takes values as Date data type and return Date data type only., Ex: ADD_MONTH ( ), SYSDATE ( ), etc, ❖ Conversion Functions: These are functions that help us to convert a value in one form to another, form., Ex: TO_CHAR ( ), TO_DATE ( ), etc, 2, , Group Functions:, ➢ COUNT ( ):, This function is used to count the number of values in the column., Ex: SELECT COUNT (Name) FROM Student;, ➢ AVG ( ):, This function is used to find the average of the values in a numeric column., Ex: SELECT AVG (Marks) FROM Student;, ➢ SUM ( ):, This function is used to find the sum of the column., Ex: SELECT SUM (Marks) FROM Student;, ➢ MAX ( ):, This function is used to find the maximum value of the column., Ex: SELECT MAX (Marks) FROM Student;, ➢ MIN ( ):, This function is used to find the minimum value of the column., Ex: SELECT MIN (Marks) FROM Student;, , Department of Computer science, , 7, , M.V.KIRANKUMAR