What is SQL?

 SQL is Structured Query Language, which is a computer language for storing,

manipulating and retrieving data stored in a relational database.
 SQL is the standard language for Relational Database System. All the
Relational Database Management Systems (RDMS) like MySQL, MS Access,
Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard
database language.
 Why SQL?
SQL is widely popular because it offers the following advantages −
◦ Allows users to access data in the relational database management systems.
◦ Allows users to describe the data.
◦ Allows users to define the data in a database and manipulate that data.
◦ Allows to embed within other languages using SQL modules, libraries &
pre-compilers.
◦ Allows users to create and drop databases and tables.
◦ Allows users to create view, stored procedure, functions in a database.
◦ Allows users to set permissions on tables, procedures and views.
 Advantages of SQL
There are the following advantages of SQL:
◦ High speed
Using the SQL queries, the user can quickly and efficiently retrieve a large amount of
records from a database.
◦ No coding needed
In the standard SQL, it is very easy to manage the database system. It doesn't require a
substantial amount of code to manage the database system.
◦ Portability
SQL can be used in laptop, PCs, server and even some mobile phones.
◦ Interactive language
SQL is a domain language used to communicate with the database. It is also used to
receive answers to the complex questions in seconds.
◦ Multiple data view
Using the SQL language, the users can make different views of the database structure.
 char(n). Fixed length character string, with user-specified length n.
 varchar(n). Variable length character strings, with user-specified
maximum length n.
 int. Integer (a finite subset of the integers that is machine-dependent).
 smallint. Small integer (a machine-dependent subset of the integer domain
type).
 number(p,d). Fixed point number, with user-specified precision of p
digits, with d digits to the right of decimal point. (ex., numeric(3,1),
allows 44.5 to be stores exactly, but not 444.5 or 0.32)
 real, double precision. Floating point and double-precision floating point
numbers, with machine-dependent precision.
 float(n). Floating point number, with user-specified precision of at least n
digits.
 Date: It is used to store the year, month and days value
 Time :It is used to store the hour,minutes and seconds value
 When you want to do some operations on the data in the database, then you
must have to write the query in the predefined syntax of SQL.
 The syntax of the structured query language is a unique set of rules and
guidelines, which is not case-sensitive. Its Syntax is defined and
maintained by the ISO and ANSI standards.
 Following are some most important points about the SQL syntax which are
to remember:
◦ You can write the keywords of SQL in both uppercase and lowercase, but writing the SQL
keywords in uppercase improves the readability of the SQL query.
◦ SQL statements or syntax are dependent on text lines. We can place a single SQL
statement on one or multiple text lines.
◦ SQL statements always start with the keywords.
◦ SQL statement ends with a semicolon.
 SQL commands are instructions. It is used to communicate with the
database. It is also used to perform specific tasks, functions, and queries of
data.
 SQL can perform various tasks like create a table, add data to tables, drop
the table, modify the
 Types of SQL Commands
◦ There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
1. Data Definition Language (DDL)
 DDL changes the structure or schema of the table like creating a table,
deleting a table, altering a table, etc.

 Here are some commands that come under DDL:

◦ CREATE
◦ ALTER
◦ DROP
◦ TRUNCATE
 CREATE It is used to create a new table in the database.
 Syntax:CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

 Example:

 CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DA

TE);
An SQL relation is defined using the create table command:
create table r
(A1 D1, A2 D2, ..., An Dn, (integrity-constraint1),
..., (integrity-constraintk))
◦ r is the name of the relation
◦ each Ai is an attribute name in the schema of relation r
◦ Di is the data type of values in the domain of attribute Ai
 Example: create table instructor ( ID char(5), name varchar(20), dept_name
varchar(20) ,salary numeric(8,2))
 DESC STUDENTS;
b. DROP: It is used to delete both the structure and record stored in the table.
Syntax
 DROP TABLE table_name;

Example
 DROP TABLE EMPLOYEE;
c. ALTER: It is used to alter the structure of the database. This change
could be either to modify the characteristics of an existing attribute or
probably to add a new attribute.
Syntax:
To add a new column in the table
 ALTER TABLE table_name ADD column_name COLUMN-
definition;
To modify existing column in the table:
 ALTER TABLE table_name MODIFY(column_definitions....);
 ALTER TABLE table_name DROP COLUMN column_name;
EXAMPLE
Alter Table Stu_details Add(address Varchar2(20));
Alter Table Stu_details Modify (Name Varchar2(20));
d. TRUNCATE: It is used to delete all the rows from the table and free the space
containing the table.
A truncate SQL statement is used to remove all rows (complete data) from a table. It is
similar to the DELETE statement with no WHERE clause.
TRUNCATE TABLE Vs DROP TABLE
 Drop table command can also be used to delete complete table but it deletes table
structure too. TRUNCATE TABLE doesn't delete the structure of the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
e. RENAME
Syntax of RENAME
RENAME old_table _name To new_table_name ;
RENAME Cars To Car_2021_Details ;

ALTER TABLE table _name RENAME column old_table_name to

new_table_name;
 Data Manipulation Language (DML) allows you to modify the database
instance by inserting, modifying, and deleting its data. It is responsible for
performing all types of data modification in a database.
 There are three basic constructs which allow database program and user to
enter data and information are:
 INSERT
 UPDATE
 DELETE
 SQL INSERT statement is a SQL query .it is used to insert a single or a
multiple records in a table.

 Syntax:
 INSERT INTO table_name VALUES(value1,value2,value3…);

 Example:
 INSERT INTO
STUDENTS(ROLL_NO,NAME,AGE,CITY)VALUES(1,’yadnyesh’,’19’,
’PUNE’);
 The UPDATE statement is used to modify the existing records in a table.

 Syntax:
 UPDATE table_name
 SET column1_value1,column2=value2,…WHERE condition;

 Example:
 UPDATE customers SET contactName =‘yadu’,city=‘pune’ WHERE
CustomerID =101;
 The SQL DELETE statement is used to delete rows from a table.
Generally, DELETE statement removes one or more records form a table.
◦ DELETE FROM table_name WHERE some_condition;
◦ DELETE FROM EMPLOYEE WHERE EMP_NAME = 'Kristen‘
◦ Delete all of the records
Delete all the row from the EMPLOYEE table. After this, no records left
to display. The EMPLOYEE table will become empty.
 Syntax
◦ DELETE * FROM table_name; or
◦ DELETE FROM table_name;
 Data Query Language (DQL) is used to fetch the data from the database. It
uses only one command:
 SELECT:
 This command helps you to select the attribute based on the condition
described by the WHERE clause.
 Syntax:
SELECT expressions FROM TABLES WHERE conditions;
 For example:
SELECT FirstName FROM Student WHERE RollNo > 15;
 SQL aggregation function is used to perform
the calculations on multiple rows of a single
column of a table. It returns a single value.
 It is also used to summarize the data.
 Various Aggregate Functions
 1) Count()
 2) Sum()
 3) Avg()
 4) Min()
 5) Max()
 Product_mast
COUNT FUNCTION
COUNT function is used to Count the number of rows in a database table. It can
work on both numeric and non-numeric data types.
COUNT function uses the COUNT(*) that returns the count of all the rows in a
specified table.
COUNT(*) considers duplicate and Null.
 Syntax
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )
 SELECT COUNT(*) FROM PRODUCT_MAST;

 10

 SELECT COUNT(*) FROM PRODUCT_MAST WHERE RATE>=20;

 7

 SELECT COUNT(DISTINCT COMPANY)FROM PRODUCT_MAST;

 3

 SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP

BY COMPANY;
 Com1 5
Com2 3
Com3 2
 Sum function is used to calculate the sum of all selected columns. It works
on Syntax
 SUM()
or
SUM( [ALL|DISTINCT] expression ) n numeric fields only.
 SELECT SUM(COST) FROM PRODUCT_MAST;
 320

 SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3

GROUP BY COMPANY;

 Com1 150
Com2 170
 The AVG function is used to calculate the average value of the numeric
type. AVG function returns the average of all non-Null values.
 Syntax
AVG()
or
AVG( [ALL|DISTINCT] expression )
 Example:
 SELECT AVG(COST) FROM PRODUCT_MAST;
 67.00
 MAX function is used to find the maximum value of a certain column. This
function determines the largest value of all selected values of a column.
 MAX()
 or
 MAX( [ALL|DISTINCT] expression )
 Example:
 SELECT MAX(RATE) FROM PRODUCT_MAST;
 30
 MIN function is used to find the minimum value of a certain column. This
determines the smallest value of all selected values of a column.
function
 Syntax

 MIN()

 or

 MIN( [ALL|DISTINCT] expression )

 SELECT MIN(RATE) FROM PRODUCT_MAST;

 10
 The SQL clauses are of three types as shown below−

 The GROUP BY Statement in SQL is used to arrange identical data into
groups with the help of some functions. i.e if a particular column has same
values in different rows then it will arrange these rows in a group.
Important Points:
◦ GROUP BY clause is used with the SELECT statement.
◦ In the query, GROUP BY clause is placed after the WHERE clause.
◦ In the query, GROUP BY clause is placed before ORDER BY clause if used any.
◦ In the query , Group BY clause is placed before Having clause .
◦ Place condition in the having clause
 Syntax:
 SELECT column1, function_name(column2) FROM table_name WHERE
condition GROUP BY column1, column2 ORDER BY column1, column2;
◦ function_name: Name of the function used for example, SUM() ,
AVG().
◦ table_name: Name of the table.
◦ condition: Condition used.
 Group By single column means, to place all the rows with same value of
only that particular column in one group. Consider the query as shown
below:
 SELECT NAME, SUM(SALARY) FROM Employee GROUP BY NAME;
 The above query will produce the below output:
 Group by multiple column is say for example, GROUP BY column1,
column2. This means to place all the rows with same values of both the
columns column1 and column2 in one group.
 Consider the below query:
 SELECT SUBJECT, YEAR, Count(*) FROM Student GROUP BY
SUBJECT, YEAR;
 As you can see in the above output the students with both same SUBJECT
and YEAR are placed in same group. And those whose only SUBJECT is
same but not YEAR belong to different groups. So here we have grouped
the table according to two columns or more than one column.
 We know that WHERE clause is used to place conditions on columns but
what if we want to place conditions on groups? This is where HAVING
clause comes into use.
 We can use HAVING clause to place conditions to decide which group will
be the part of final result-set.
 .Syntax:
 SELECT column1, function_name(column2) FROM table_name WHERE
condition GROUP BY column1, column2 HAVING condition ORDER BY
column1, column2;
◦ function_name: Name of the function used for example, SUM() , AVG().
◦ table_name: Name of the table.
◦ condition: Condition used.
 SELECT NAME, SUM(SALARY) FROM Employee GROUP BY
NAME HAVING SUM (SALARY)>3000
 As you can see in the above output only one group out of the three groups
appears in the result-set as it is the only group where sum of SALARY is
greater than 3000.
 So we have used HAVING clause here to place this condition as the
condition is required to be placed on groups not columns.;
 This clause sorts the result in either ascending or descending order.
 By default, it does an ascending order if you don’t mention anything. ASC
and DESC are the keywords used to order the records.
 The syntax is as follows −
 SELECT column1, column2 FROM table_name ORDER BY
column_name ASC|DESC;
 table_name: name of the table.

 column_name: name of the column according to which the data is needed

to be arranged.
 ASC: to sort the data in ascending order.
 DESC: to sort the data in descending order.
 To sort in ascending or descending order we can use the keywords ASC or
DESC respectively. To sort according to multiple columns, separate the
names of columns by the (,) operator.
 Syntax:
 SELECT * FROM table_name ORDER BY column1 ASC|DESC ,
column2 ASC|DESC
Sort according to multiple columns:
SELECT * FROM Student ORDER BY Age ASC , ROLL_NO DESC;
 There are three types of comments, which are
given below:
 Single line comments.
 Multi-line comments
 Single Line Comment
 Comments starting and ending with a single line are said as
individual line comments. The line which starts with '–' is a
single line comment, and that particular line is not executed.
 --Select all:
 Multi-line Comments
 Comments that start in one line and end in different front are
said as multi-line comments. The text between /* and */ is
ignored in the code part.
 The line starting with '/*' is considered as a starting point of
comment and terminated when '*/' lies at the end.
 /* multi-line comment
 another comment */
 SELECT * FROM Customers;
 Operator Description

+ Add

- Subtract

* Multiply

/ Divide

% Modulo

SELECT Salary +30 from employee

SELECT Salary -30 from employee

SELECT Salary *0.25 from employee
SELECT Salary /5 from employee

SELECT Salary %2 from employee

The SQL Operators which compare the values of two columns in the database tables
are called as comparison operators.

In SQL, comparison operators are always used in the WHERE clause with the
SELECT, UPDATE, and DELETE statements.

Operator Description
= Equal to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to

SELECT * FROM Table_Name WHERE Column_Name = Value;

 SET operators are special type of operators which are used to
combine the result of two queries.
 Operators covered under SET operators are:
◦ UNION
◦ UNION ALL
◦ INTERSECT
◦ MINUS
 There are certain rules which must be followed to perform
operations using SET operators in SQL. Rules are as follows:
 The number and order of columns must be the same.
 Data types must be compatible.
 UNION will be used to combine the result of two select
statements.
 Duplicate rows will be eliminated from the results obtained
after performing the UNION operation.
 Example 1:
 SELECT *FROM t_employees UNION SELECT *FROM t2_employ
ees;
 This operator combines all the records from both the queries.
 Duplicate rows will be not be eliminated from the results
obtained after performing the UNION ALL operation.
 SELECT *FROM t_employees UNION ALL SELECT *FROM t2_em
ployees;
 It is used to combine two SELECT statements, but it only returns the
records which are common from both SELECT statements.
 SELECT *FROM t_employees INTERSECT SELECT *FROM t2_em
ployees;
 Here, in a single query, we have written two SELECT queries. The first
SELECT query will fetch the records from the t_employees table and
perform INTERSECT operation with the records fetched by the second
SELECT query from the t2_employees table.
 It displays the rows which are present in the first query but absent in the
second query with no duplicates.
 SELECT *FROM t_employees MINUS SELECT *FROM t2_employees;
 The select clause lists the attributes desired in the result of a query
◦ corresponds to the projection operation of the relational algebra
 Example: find the names of all instructors:
select name from instructor
 An asterisk in the select clause denotes “all attributes”
select *from instructor
 SQL allows duplicates in relations as well as in query results.
 To force the elimination of duplicates, insert the keyword distinct
after select.
 Find the department names of all instructors, and remove duplicates
select distinct dept_name from instructor
 The keyword all specifies that duplicates should not be removed.
select all dept_name from instructor
 To force the elimination of duplicates, insert the keyword distinct
after select.
 The keyword all specifies that duplicates should not be removed.
select all dept_name from instructor
 The select clause can contain arithmetic expressions involving the
operation, +, –, , and /, and operating on constants or attributes of
tuples.
◦ The query:
select ID, name, salary/12
from instructor
would return a relation that is the same as the instructor relation,
except that the value of the attribute salary is divided by 12.
◦ Can rename “salary/12” using the as clause:
select ID, name, salary/12 as monthly_salary
 The where clause specifies conditions that the result must satisfy
◦ Corresponds to the selection predicate of the relational algebra.
 To find all instructors in Comp. Sci. dept
select name
from instructor
where dept_name = 'Comp. Sci.'
 SQL allows the use of the logical connectives and, or, and not
 The operands of the logical connectives can be expressions
involving the comparison operators <, <=, >, >=, =, and <>.
 Comparisons can be applied to results of arithmetic expressions
 To find all instructors in Comp. Sci. dept with salary > 70000
select name from instructor where dept_name = 'Comp. Sci.' and
salary > 70000
 The from clause lists the relations involved in the query
◦ Corresponds to the Cartesian product operation of the relational
algebra.
 Find the Cartesian product instructor X teaches
select  from instructor, teaches
◦ generates every possible instructor – teaches pair, with all
attributes from both relations.
◦ For common attributes (e.g., ID), the attributes in the resulting
table are renamed using the relation name (e.g., [Link])
 Cartesian product not very useful directly, but useful combined
with where-clause condition (selection operation in relational
algebra).
 Find the names of all instructors who have taught some course and
the course_id
select name, course_id from instructor , teaches where
[Link] = [Link]

 Find the names of all instructors in the Art department who have
taught some course and the course_id
 select name, course_id
from instructor , teaches
where [Link] = [Link] and instructor. dept_name =
'Art'
 The SQL allows renaming relations and attributes using the as clause:
old-name as new-name

 Find the names of all instructors who have a higher salary than
some instructor in 'Comp. Sci'.
◦ select distinct [Link]
from instructor as T, instructor as S
where [Link] > [Link] and S.dept_name = 'Comp. Sci.’

 Keyword as is optional and may be omitted

instructor as T ≡ instructor T
  The LIKE clause uses the following symbols known as
wildcard operators in SQL to perform this pattern-matching
task in SQL.
 To represent zero, one or more than one character, %
(percentage) is used.
 To represent a single character _ (underscore) is used.

ID Name City Salary Age

1 Priyanka Nasik 26000 20

Bagul
2 Riya Sharma Mumbai 72000 28
3 Neha Verma Varanasi 37000 19
4 Neeta Desai Nasik 39500 21
5 Priya Wagh Udaipur 60000 32
  Write a query to display employee details in which name starts with 'Pr'.
SELECT * FROM employee_details WHERE Name LIKE 'Pr%';
 Write a query to display employee details in which 'ya' is a substring in a
name.
SELECT * FROM employee_details WHERE Name LIKE '%ya%';
 Write a query to display employee details in which city name ends with 'i'.

SELECT * FROM employee_details WHERE City LIKE '%i';

 Write a query to display employee details in which age number starts with
2.
SELECT * FROM employee_details WHERE Age LIKE '2%';
 Write a query to display employee details in which salary contains a
number starting with '3' succeeding any two digits and finally ends with
'00'.
SELECT * FROM employee_details WHERE Salary LIKE '3__00';
 Write a query to display employee details in which employee name
contains 'a' at fifth position.
SELECT * FROM employee_details WHERE Name LIKE '____a%';
 It is possible for tuples to have a null value, denoted by null, for
some of their attributes
 null signifies an unknown value or that a value does not exist.
 The result of any arithmetic expression involving null is null
◦ Example: 5 + null returns null
 The predicate is null can be used to check for null values.
◦ Example: Find all instructors whose salary is null.
select name
from instructor
where salary is null
 The predicate is not null succeeds if the value on which it is
applied is not null.
 SQL treats as unknown the result of any comparison involving a
null value (other than predicates is null and is not null).
◦ Example: 5 < null or null <> null or null = null
 The predicate in a where clause can involve Boolean operations
(and, or, not); thus the definitions of the Boolean operations need
to be extended to deal with the value unknown.
◦ and : (true and unknown) = unknown,
(false and unknown) = false,
(unknown and unknown) = unknown
◦ or: (unknown or true) = true,
(unknown or false) = unknown
(unknown or unknown) = unknown
 Result of where clause predicate is treated as false if it evaluates
to unknown
 DCL commands are used to grant and take back authority from any database
user.
 Grant

 Revoke

a. Grant: It is used to give user access privileges to a database.

Example
 GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHE
R_USER;
◦ GRANT SELECT,UPDATE ON PERSONS TO LIBRARY
 TO CHECK :
 select * from [Link]

IN THE LIBRARY DATABASE

 b. Revoke: It is used to take back permissions from the user.

 Example

 REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

◦ REVOKE SELECT ON PERSONS FROM LIBRARY
 TCL commands can only use with DML commands like INSERT,
DELETE and UPDATE only.
 These operations are automatically committed in the database that's why
they cannot be used while creating tables or dropping them.
 Here are some commands that come under TCL:
◦ COMMIT
◦ ROLLBACK
◦ SAVEPOINT
a. Commit: Commit command is used to save all the transactions to the
database.
 Syntax: COMMIT;
 Example:
 DELETE FROM CUSTOMERS WHERE AGE = 25;
 COMMIT;
b. Rollback: Rollback command is used to undo transactions that have not
already been saved to the database.
 Syntax:

 ROLLBACK;

Example:
 DELETE FROM CUSTOMERS WHERE AGE = 25;

ROLLBACK;
 c. SAVEPOINT: It is used to roll the transaction back to a certain point
without rolling back the entire transaction.
 Syntax:

 SAVEPOINT SAVEPOINT_NAME;
 SQL constraints are used to specify rules for data in a table.
 SQL Create Constraints
 Constraints can be specified when the table is created with the CREATE
TABLE statement, or after the table is created with the ALTER
TABLE statement.
 Constraints are used to limit the type of data that can go into a table. This
ensures the accuracy and reliability of the data in the table. If there is any
violation between the constraint and the data action, the action is aborted.
 Constraints can be column level or table level. Column level constraints
apply to a column, and table level constraints apply to the whole table.
 A CONSTRAINT can be one of the following:a column-level constraint

 Column-level constraints refer to a single column in the table and do

not specify a column name (except check constraints). They refer to the
column that they follow.
SYNTAX:COLUMN[CONSTRAINT CONSTRAINT_NAME
CONSTARINT_TYPE],)
 a table-level constraint: Table-level constraints refer to one or more
columns in the table. Table-level constraints specify the names of the
columns to which they apply. Table-level CHECK constraints can refer to 0
or more columns in the table.
 SYNTAX:COLUMN[CONSTRAINT CONSTRAINT_NAME
CONSTARINT_TYPE(COLUMN)],)
 NAMING:TABLENAME_FIELDNAME_CONSTRAINTNAME
 NOT NULL - Ensures that a column cannot have a NULL value
 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE.
Uniquely identifies each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between
tables
 CHECK - Ensures that the values in a column satisfies a specific
condition
 DEFAULT - Sets a default value for a column if no value is
specified
 By default, a column can hold NULL values.
 The NOT NULL constraint enforces a column to NOT accept NULL
values.
 This enforces a field to always contain a value, which means that you
cannot insert a new record, or update a record without adding a value to
this field.
SQL NOT NULL on CreateTABLE
• CREATE TABLE Persons ( ID int NOT NULL,LastName
varchar(255) NOT NULL,FirstName varchar(255) NOT NULL,Age int)

SQL NOT NULL on ALTER TABLE

 ALTER TABLE Persons MODIFY Age int NOT NULL;
 ALTER TABLE Persons MODIFY Age int NULL;
 The UNIQUE constraint ensures that all values in a column are different.
 Both the UNIQUE and PRIMARY KEY constraints provide a guarantee
for uniqueness for a column or set of columns.
 A PRIMARY KEY constraint automatically has a UNIQUE constraint.

 However, you can have many UNIQUE constraints per table, but only
one PRIMARY KEY constraint per table.
SQL UNIQUE Constraint on CREATE TABLE
 The following SQL creates a UNIQUE constraint on the "ID" column
when the "Persons" table is created:
◦ CREATE TABLE Persons (ID int UNIQUE, LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int);
 CREATE TABLE Persons2 ( ID int, LastName varchar(255) NOT NULL,
FirstName varchar(255),Age int, CONSTRAINT UC_Person UNIQUE
(ID,LastName));

 SQL UNIQUE Constraint on ALTER TABLE

 For one column
◦ ALTER TABLE Persons ADD UNIQUE (ID);
 For multiple columns
◦ ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
 Drop the unique constraint
◦ ALTER TABLE Persons DROP CONSTRAINT UC_Person;
SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='PERSONS’
◦ ALTER TABLE Persons DROP CONSTRAINT UC_PERSONS
◦ ALTER TABLE PERSONS DROP CONSTRAINT SYS_C004058
 The DEFAULT constraint is used to set a default value for a column.
 The default value will be added to all new records, if no other value is
specified.
◦ CREATE TABLE Persons ( ID int NOT NULL,LastName varchar(255) NOT NULL,
FirstName varchar(255),Age int,City varchar(255) DEFAULT 'Sandnes');

◦ ALTER TABLE Persons MODIFY City DEFAULT 'Sandnes';

FOR DROP DEFAULT CONSTRAINT
◦ ALTER TABLE Persons MODIFY City DEFAULT NULL;
NOTE:
If a column has a default value, then you can use the DEFAULT clause to
change the default to NULL, but you cannot remove the default value
completely. If a column has ever had a default value assigned to it, then the
DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary
view will always display either a default value or NULL.
 The CHECK constraint is used to limit the value range that can be placed
in a column.
 If you define a CHECK constraint on a column it will allow only certain
values for this column.
 If you define a CHECK constraint on a table it can limit the values in
certain columns based on values in other columns in the row.
 SQL CHECK on CREATE TABLE
 The following SQL creates a CHECK constraint on the "Age" column
when the "Persons" table is created. The CHECK constraint ensures that
the age of a person must be 18, or older:
◦ CREATE TABLE Persons ( ID int NOT NULL,LastName varchar(255) NOT NULL,
FirstName varchar(255), Age int CHECK (Age>=18));
◦ CREATE TABLE Persons ( ID int NOT NULL,LastName varchar(255) NOT NULL,
FirstName varchar(255), Age int, City varchar(255), CONSTRAINT
◦ CHK_Person CHECK (Age>=18 AND City='Sandnes'));
 To create a CHECK constraint on the "Age" column when the table is
already created, use the following SQL:
◦ ALTER TABLE Persons ADD CHECK (Age>=18);
 To allow naming of a CHECK constraint, and for defining
a CHECK constraint on multiple columns, use the following SQL syntax:
◦ ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
 DROP a CHECK Constraint
 To drop a CHECK constraint, use the following SQL:

◦ ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;

◦ SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='PERSONS’
◦ ALTER TABLE Persons DROP CONSTRAINT SYS_C004058
◦ ;
 The PRIMARY KEY constraint uniquely identifies each record in a table.
 Primary keys must contain UNIQUE values, and cannot contain NULL
values.
 A table can have only ONE primary key; and in the table, this primary key
can consist of single or multiple columns (fields).
◦ CREATE TABLE Persons ( ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,FirstName varchar(255),
Age int
);
 To allow naming of a PRIMARY KEY constraint, and for defining
a PRIMARY KEY constraint on multiple columns, use the following SQL
syntax:
◦ CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL,
FirstName varchar(255),Age int
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName));
 In the example above there is only ONE PRIMARY KEY (PK_Person).
However, the VALUE of the primary key is made up of TWO COLUMNS
(ID + LastName).
 To create a PRIMARY KEY constraint on the "ID" column when the table
is already created, use the following SQL:
◦ ALTER TABLE Persons ADD PRIMARY KEY (ID);
 To allow naming of a PRIMARY KEY constraint, and for defining
a PRIMARY KEY constraint on multiple columns, use the following SQL
syntax:
◦ ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
 Note: If you use ALTER TABLE to add a primary key, the primary
key column(s) must have been declared to not contain NULL values
(when the table was first created).
 DROP a PRIMARY KEY Constraint
 To drop a PRIMARY KEY constraint, use the following SQL:
◦ ALTER TABLE Persons DROP PRIMARY KEY;
◦ ALTER TABLE Persons DROP CONSTRAINT PK_Person;
 The FOREIGN KEY constraint is used to prevent actions that would
destroy links between tables.
 A FOREIGN KEY is a field (or collection of fields) in one table, that refers
to the PRIMARY KEY in another table.
 The table with the foreign key is called the child table, and the table with
the primary key is called the referenced or parent table.
 Look at the following two tables:
 Persons table Order table
OrderID OrderNumber PersonID
PersonID LastName FirstName Age
1 Hansen Ola 30 1 77895 3
2 Svendson Tove 23 2 44678 3
3 Pettersen Kari 20 3 22456 2

4 24562 1
 The FOREIGN KEY constraint prevents invalid data from being inserted
into the foreign key column, because it has to be one of the values
contained in the parent table.
 SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column

when the "Orders" table is created:
◦ CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,PersonID
int FOREIGN KEY REFERENCES Persons(PersonID));
 To allow naming of a FOREIGN KEY constraint, and for defining
a FOREIGN KEY constraint on multiple columns, use the following SQL
syntax:
◦ CREATE TABLE Orders ( OrderID int NOT NULL,OrderNumber
int NOT NULL,PersonID int,PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID));
 To create a FOREIGN KEY constraint on the "PersonID" column when the
"Orders" table is already created, use the following SQL:
◦ ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
 To allow naming of a FOREIGN KEY constraint, and for defining
a FOREIGN KEY constraint on multiple columns, use the following SQL
syntax:
◦ ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
 To drop a FOREIGN KEY constraint, use the
following SQL:
 ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;