Unit-2 CONCEPTS OF SQL

2.0 Concepts of SQL

2.1 Explain SQL and benefits of SQL.

2.2 Describe about Embedded SQL and Lexical conventions

2.3 Describe Naming of the Objects and parts and how to refer them.
2.4 Explain literals & different data types like character, number, long, date, raw and long raw

2.5 Illustrate the comments within SQL Statement

2.6 Explain SQL Operators

2.7 Describe Data Definition Language commands CREATE, ALTER and DROP.
2.8 Explain integrity constraints through creating a table and altering table.

2.9 Describe Data Manipulation Language commands INSERT, UPDATE and DELETE
2.10 Explain SELECT statement with WHERE, ORDER BY, GROUP BY and HAVING clauses with examples
2.11 List and explain single row (Number, character, date and conversion) functions

2.12 List and Explain group functions

2.13 Explain Transaction Control Commands COMMIT, SAVEPOINT, ROLLBACK, GRANT, and REVOKE.
2.14 Explain Sub queries with examples

2.15 Explain Joins (Equi Join, Non-Equi Joins, Inner Join, Outer Join, cross join and Self join) with syntax and examples.
2.1 SQ1 & Benifits of SQL
Structured Query Language specially designed to interact with relational database.
It is a language for storing, manipulating & retriving data in RDBMS. The software that
supporting for sql are Oracle, MS Access, Informix, MS SQL etc.
It is used to control the functions of DBMS for users including

Data Definition: Sql allows was to define Structure & organization of the stored data &
relationship among the stored data.
Data Retrieval: allows users to retrieve. the stared data from the database & use it.

Data Manipulation: allows users to update the database by adding new data & removing old
data & modifying previous data.
Access control: restrict the ability to retrieve, add & modify Protecting stored data against
unauthorized users.

Data sharing:-Used to coordinate data sharing by Concurrent users, ensuring they don't interface
with one another
Data integrity: Integrity constraints in database, protecting it. from inconsistent updates &
system failures

Types of sql statements: DDL, DML, DCL & TCL

Benefits of Sql:
Sql standard: In 1986 & 1987, ANSI (American National standards Institute & Iso international
standard organization) Standardized language. The most recent standard part was produced in
2011
Portable: Sql rans on Pcs, laptops, servers, tables, & Smart phones. It runs in local system
intranet & internet. It can be moved from device to another.

open source: Fiee databases from MySQL, mariaDB etc Can be sql databases can be used in low
cost large Communities behind them
Used by major DBMS System vendors: Sql used by all vendors such as IBM, Oracle &
microsoft who develop dbms.
Easy to learn & understand: Queries are like English statement makes easy to Learn & write.
Interactive language: Sql used to communicate with db & get answers for Complex Queries in
Seconds
Complete Language for a database: Sal create db, updating, retrieving & sharing data with
multiple users, and also data remains accurate, secure & consistent

Multiple data views: different users of dib can have different views of structure & content of db.
Client/server language: Sql used to connect client's (front end computers) & servers (back end
computers),Thus supports Client Server architecture.

2.2 DESCRIBE ABOUT EMBEDDED SOL AND LEXICAL CONVENTIONS

Embedded Sat combines high level language to do language. Most sql statements embedded in
host programming language Such as C, Java.

It distinguished by enclosing it between EXEC SQL BEGIN & matching EXEC SQL END(;).
The high level language which supports Embedding SQLs is known as host language. when
SOL Embedded with a language then Compilation poses two steps:
1.Extract sql codes & pre compiler will complete Sal oracle from its Syntax, execution path.
2.Once pre-compilation is done, these executable codes embedded into Code then the compiler
compile & execute the code.
compilation will be done in two steps-ore for sql gone for application Language. this
compilation needs Query, data value. the compilation time itself to generate executable code.
Otherwise any high level language cannot compile the code.

 Hence Sql codes are static & Embedded Sql is knowns static sql. Thus sql know how to
access DB, which Queries to execute, values to insert/delete/update
Structure of Embedded sql:-

↳ It shows the process of establishing connection & Executing code in DB within high level
language

(i)Connection of DB
step 1-Connecting DB. will be done by using keyword CONNECT can be procced with "Exec
sql” indicates Sql statement

"EXEC SQL CONNECT db_name;”

EXEC SQL CONNECT HR USER; 11 Connect to HR USER

(ii) variable declaration:

Once’s Connection is done-transactions takes place which depend on values &variables. We
need to declare variables to pass the values.

* Host variables that used to pass the value to the Query & returns the values - this variables
should be within the Sql so that Compiler differentiate from normal c variables between Begin
Declare & end declare
EXEC SQL BEGIN DECLARE SECTION;

int STD_ID;
Char STD_name[15];

Char ADDRESS [20];

EXEC SQL END DECLARE SECTION;

Executing Sql commands

EXEC SQL SELECT FROM STUDENT WHERE STUDENTID=:STD_ID;
EXEC SQL SELECT STD_NAME INTO : SNAME: IND_SNAME FROM STUDENT
WHERE STUDENT_ID =:STD_ID;

INSERT INTO STUDENT (STD_ID, STD_NANE) VALUES C:SID:SNAME);

UPDATE STUDENT SET ADDRES=: STD_ADDR WHERE STDL_ID =:SID;
Example: EXEC SQL BEGIN DECLARE SECTION;

NAME CHAR (15);

SALARY FLOAT;

EXEC SQL END DECLARE SECTION;

EXEL SQL CONNECT PERSONNEL;

EXEC SQL SELECT ENAME, SAL INTO: NAME: SALARY FROM EMPLOYEE WHERE
END=23;
PRINT NAME, SALARY;

EXEC SOL DISCONNECT;

LEXICAL CONVENTION:
SQL statement are composed of commands, Variables, & Operators
Sql constructed from, Characters (A tot)..

Number (0 through 9), spaces

Special characters (+,-,*,=,?,@,(,),-,.,>,|,$,#)
SELECT Last_name, Salary*12, Months_Between (hire-date, SYSDATE) FROM Employees;
(or)
SELECT Last-name,Salary*12,MONTHS BETWEEN (hire_date, SYSDATE) FROM
Employees;

2.3 NAMING OF THE OBJECCTS AND PARTS AND HOWTO REFER THEM
Naming rules used to increase the readability of data which user apply rules while naming
anything in db, including tables, columns Primary & foreign keys, functions, views etc. for
instance, it would be f to tables & Columns Perfectly to limit naming Conversion rules to tables
& columns.

Naming Rules:
→ Only letters, numbers & underscores are allowed (Oracle allows $ and # which causes
unexpected problems.
→ All names in UPPER CASE (no importance which care it help to & identify easily without
clumsy) rale can be ignored.

→ Name should start with letter

→ Make names meaningful, but not too long.

Referencing to an object or a part :-

you can give names to objects like tables, columns, views, functions indexes, Aliases,
Sequency, stored procedures, user_defined types.

which sql creates & maintains to an object of a part.

database_object_or_part ::= [schema.] object [.part] [@dblink.
*schema = Owner of object (we must be wanted privileges refer to object otherwise Oracle refus
to an object in your own schema)
*Object = the thing refering to name (Like table of view)
* Part = Special part of the table (like a column )

*@dblink = It is used when the object is in other db or remote db If you not use @dblink it will
not allows you to accesses.

How oracle database Resolves schema object References

When you refer to an object with sql statement then, Oracle locates the Object with appropriate
namespace then it perform Operation. if no named appropriate namespace it return error.
"INSERT INTO department VALUES (280, 'Entertainment_clerk’, 206, 1700);

Oracle determine departments.

--A table is own schema (own of your db space)

--A view is your Schema

--A private synonym (shortcut) for table/views)

--A public synonym

If oracle can't find, you will get error.

1)Oracle first cooks the Object in your own schema (your db) if the object with private synonym
it will point to your schema point to of another schema! If it. points to another synonym, oracle
will follow the chain until of finds actual object
2) Oracle finds the object, it perform the sql operation (Like insert) which is correct type of
object.
for INSERT, the object must be table or view (8f it is not in sequence oracle shows errors).
3) The object isn't in your schema of private synonyms then Oracle looks in Public Synonyms
(Shortcut available to everyone in db) still not find then it gives the error.
*Rule of Synonyms:
*Public Synonym has dependent Objects tables of user defined) you can't Create another object
with Same name
--If doesn't have dependent Object you can create new object with same name

Reference to objects in other schemas-

--If you want to user or drop in another user's schema you must prefix the object name with
schema name Schema Object

Ex: DROP TABLE hr. employees; (delete the employees tables on the hr schema)

Reference to object in Remote database-

--when you want to access the object from another database uses the object name with the name
of database link to db.
→dblink is a schema object that connects Oracle to remote database to access the Object.
[database link acts like a bridge to connect oracle. to your Current do to another db]

Create a dblink you need to provide: -

→name of the link

→ the connection string (where to find the remote db)

→username & password to connect to remote db

*Oracle stores this link in its Internal system(data dictionary)

database link names:

→The name of the database link is different from regular Object names.
It can contain dots (.) and @sign

It can be upto 128 bytes long.

Syntax of database dink name: database [domain. [domain...]] (@connection-qualifier]
*Database: The remote databases global name to which database ink connects.

→ You can see this name in GLOBAL_NAME in data dictionary view

*Domain: domain part of the database global name in remote db connected by database link.
If you remove domain, then oracle Qualify db link. name with domain of local db as currently
exist in data dictionary.
*@connection-qualifier: It allows to you to create multiple database links of Same db.
→ used to tune the Connection such as when working with oracle Real Application Cluster that
access the Same database.

2.4 LITERALS & DIFFERENT DATA TYPES LIKE CHARACTER, NUMBER, LONG,
DATE, RAW AND LONG RAW ETC.
A literal is an explicit numeric, character, string, or Boolean value not represented by an
identifier. Literal items and constant value are synonymous and refer to a fixed data value.
1. String Literals
2. Numeric Literals
3. Boolean Literals
4. Date and Time Literals

String Literals:
string literal examples contain strings enclosed in single quotes (‘).Use the text literal notation to
specify whenever string appears in the syntax of expressions, conditions, SQL functions, and
SQL statements.

Examples:
'apstbet.net' 'This is SQL' '09-MAR-98' '1563'

Numeric Literals:
Numeric literal notation used to specify fixed and floating-point numbers. Integer literals and
decimal literals come under this.
Integer literals can be either positive numbers or negative numbers, but do not contain
decimals. If a sign is not specified, then a positive number is assumed.

Examples: 536, -12, +234

Decimal literals:
Decimal Literals can be either positive numbers or negative numbers and contain decimals.
Examples: 25,-25,16.34,-36.03
Datetime Literals:Datetime literals are character representations of datetime values that are
enclosed in single quotes. Here are some examples of valid datetime literals:
DATE Literals:You can specify a DATE value as a string literal, or you can convert a character
or numeric value to a date value with the TO_DATE function.

Example: DATE '1998-12-25'

Using in expression: SELECT * FROM my_table WHERE datecol = DATE '2002-10-03';

TIMESTAMP Literals:
The TIMESTAMP data type stores year, month, day, hour, minute, and second, and fractional
second values.

Example: TIMESTAMP '1997-01-31 09:26:50'

SQL DATA TYPES

A data type is a category that defines the type of values that a column can hold.

A data type associates a fixed set of properties with the values that can be stored in a column of a
table or in an argument of a procedure.
For example, one property of the NUMBER data type is precision.

For example, columns can hold values of NUMBER data type, but not values of RAW data type.
Oracle supplies the following built-in data types:

1. Character data types

2. Numeric data types
3. Large Object data types
Another data type, ROWID is used for values in the ROWID pseudo column, which represents
the unique address of a row in a table.
Character Data Types:

Data type Description

It is used to store fixed length character data within the predefined

CHAR (size) length. It can be stored up to 2000 bytes.
It is used to store bounded string data within the predefined length.
VARCHAR (size) We have to specify the size of VARCHAR data type. It can be stored
up to 4000 bytes.
VARCHAR2(size) It is suggested to use VARCHAR2(size). You can also use
VARCHAR (size) but it is not ANSI compliant.
NCHAR (size) It is used to store national character data within the predefined length.
It can be stored up to 2000 bytes.
 NVARCHAR2(size) It is used to store variable length string data within the predefined
length. It can be stored up to 4000 bytes.

Numeric Data Types:

Data type Description

NUMBER (p, s) It contains precision p and scale s. The precision p can range from 1 to
38, and the scale s can range from -84 to 127.
FLOAT It is a subtype of the NUMBER data type. The precision p can range
from 1 to 126.
BINARY_FLOAT It is used for binary precision (32-bit). It requires 5 bytes, including
length byte.
BINARY_DOUBLE It is used for double binary precision (64-bit). It requires 9 bytes,
including length byte.

Date and Time Data Types:

Data type Description

DATE It is used to store a valid date-time format with a fixed length. Its range varies
from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP It is used to store the valid date in YYYY-MM-DD with time hh:mm:ss
format.

Large Object Data Types (LOB Types):

Data type Description

BLOB It is used to specify unstructured binary data. Its range goes up to 2^32 - 1 bytes or 4
GB.
BFILE It is used to store binary data in an external file. Its range goes up to 2^32 - 1 bytes
or 4 GB.
CLOB It is used for single-byte character data. Its range goes up to 2^32 - 1 bytes or 4GB.

NCLOB It is used to specify single-byte or fixed length multibyte national character set
(NCHAR) data. Its range is up to 2^32 - 1 bytes or 4 GB.
RAW(size) It is used to specify variable length raw binary data.Its range is up to 2000 bytes per
row. Its maximum size must be specified.
LONG It is used to specify variable length raw binary data. Its range up to 2^31-1 bytes or
RAW 2GB, per row
NUMBER: This data type is used to store the numeric data. This data type can be used in three
forms.
a.NUMBER: In this form we don't need to specify the number of digits to be stored in a
particular field.

Example:create table student (fees number);

b.NUMBER(P): The maximum number of digits which can be stored in a field of NUMBER
Here P is called precision.

Example: create table student (fees number (5)); //maximum five digits i.e. 99999.
c.NUMBER (P, S): Thenumber of digits after point which can be stored in a field of NUMBER
type. Here P is called precision and S is known as scale

Example: create table student (fees number (7,2)); //a numeric value of maximum seven
digits and which can on two digits after decimal point i.e. 99999.99
CHAR: This data type is used to store alphabets, digits, special characters as well as spaces. This
data type is used fixed length string data. The field of this data type can store maximum of 255
characters.

If the value stored in this type of field has lesser numberof characters than the maximum size
then blank spaces will be filled in rest of the size.
Example:create table student(name char(20)); //contain 20 characters

NCHAR:NCHAR data type is similar to the CHAR data type, except that it stores Unicode data
It can store up 00 bytes and must be shorter than 2000 characters.
Example: create table student (name nchar(20)); //maximum of 20 characters.
VARCHAR: A variable-length string data type that stores letters, digits, special characters, and
spaces, up to 2000 characters. Unused space is not wasted and can be used by other fields.

Example: create table student(name varchar(20)); //maximum of 20 characters.

VARCHAR2:A variable-length string data type that stores letters, digits, special characters, and
spaces, with a maximum of 4000 characters. Unused space is not wasted and can be used by the
next field.

Example:create table student(name varchar2(30)); //maximum of 30 characters.

NVARCHAR2: NCHAR2 is similar to VARCHAR2. NVARCHAR2 stores Unicode variable-
length data. The maximum number of characters must be specified when declaring the field.
Example: create table student(name varchar2(30)); // maximum 30 characters.
DATE: This data type stores date and time in the format DD-MON-YY. DD represents the day
(1–31), MON is the first three letters of the month (e.g., Jan, Feb, Nov), and YY is the year. For
example: 12-NOV-2005. The size is fixed and doesn't need to be specified.
Example: create table student (dateofbirth date);

LONG: This data type is also used to store variable length character string containing upto 2GB
of data. Long values can't be indexed and string operations can't be applied on field of this data
type.
Example:create table student(resume LONG);

LONG RAW/RAW: This data type stores binary data like images. RAW fields can hold up to
255 bytes, while LONG RAW fields can store up to 2GB.
Example: create table student (resume RAW);

BLOB: BLOB stores unstructured binary large objects (not character data) with a maximum size
of 4GB. Size can be specified using:
BLOB(length [K|M|G]) where K = kilobytes, M = megabytes, and G = gigabytes. If not
specified, the default size is used value is 4 GB. It's useful for storing images or audio files.

Example: create table student(photo BLOB(10k));//photo can contain upto 10 Kilo Bytes of
data.
CLOB: CLOB stores large amounts of character (text) data, not binary data, with a maximum
size of 4GB.
Size can be set using: CLOB(length [K|M|G]), where K = kilobytes, M = megabytes, and G =
gigabytes. If not specified, the default is 4GB. Useful for storing large text that exceeds
VARCHAR2 limits.

Example: CREATE TABLE student(publication CLOB);//The publication field can hold up to

4GB of text data.
BFILE: This data type is used to refer to other files on the server, which are outside the database
system. It stores a pointer to a file. They are read-only to Oracle. The maximum size is 4 GB.

pseudo-column: A pseudo-column behaves like a table column but is not actually stored in the
table. Pseudo columns are nothing but logical columns which behaves like physical columns in
database. We can select from pseudo-columns, but we cannot insert, update, or delete their
values.Some pseudo-columns are:

rowidcurrval and nextvalsysdatesystimestamprownumlevel user

Rowid, Rownum are the Pseudo columns in oracle used to select the data from tables.
ROWID: ROWID is a pseudo column in a table which store and return row address in
HEXADECIMAL format database tables. It is permanent unique identifiers for each row in the
database. It consists of 18 character string with the format, BBBBBBBBB.RRRR.FFFF Where B
is Block Row, F is file.
Example Syntax SELECT rowid, empno FROM EMPI;

ROWID is representative of the allocation of physical memory.

Oracle automatically generates a unique ROWID at the time of insertion of a row.

ROWID is the permanent identity or address of a row which one can easily access (retrieve data
from a row the rows from the table.A user can access a row quickly and easily using its row ID.
ROWID can also be used to delete normal records and the duplicate records from a table.

2.5 COMMENTS WITHIN SQL STATEMENT

A comment is a piece of text within a program that is not executed. It can be used to provide
adding information to aid in understanding the code, or to prevent execution of SQL statements.

SINGLE-LINE COMMENTS:Comments that begin and end with a single line are called
Single line comments.Single line comments start with A comment which starts with '-' is not
executed, and the text between double hyphen) and the end of the line will be ignored without
being run.
MULTI-LINE COMMENTS:Multi-line comments start with /* and end with */. Any text
between /* and */ will be ignored:
IN-LINE COMMENTS:To ignore just a part of a statement, also use the /* */ comment.
SELECT id, customer_name, /* city, */ countryFROM customers;
COMMENTS WITHIN SCHEMA OBJECTS:For comments on database objects, such as
views and tables, the syntax to add these explicit comments:

comment on [database object type) <database object name> is 'comment text here’ These
database object-level comments are more useful for adding additional context or metadata to
these inline comments being useful for explaining code functionality. Use the COMMENT
statement to a comment about a table, view, materialized view, or column into the data
dictionary.

Example:
COMMENT ON COLUMN employees.job_id IS 'describe job title';
COMMENT ON COLUMN books.author IS The main author's last name':
COMMENT ON TABLE books IS 'Contains only the best books';
To drop this comment from the database, issue the following statement:

COMMENT ON COLUMN employees.job_id IS'':

2.6 SQL OPERATORS

Operators are nothing but a character or symbol that represents an action or process. An Operator
capable of manipulating operand items and returns a result.
SQL operators are reserved keywords used in the WHERE clause of a SQL statement to perform
arithmetic, logical and comparison operations.
In SQL, an operator can either be a unary or binary operator,
Unary Operators:A unary operator uses only one operand. A unary operator typically appears
with its operand in the following format.which are performed on the numeric data stored in the
SOL table. Syntax: Operator Operand
unary +veOperator: The SOL Positiveoperator makes the numeric value of the SQL table +ve

unary Negative Operator: TheSQL Negative (-) operator makes the numeric value of the SOL
table negative.
unary Bitwise NOT Operator: The SOL Bitwise NOT ~ operator provides the one's
complement of the single numeric operand. This operator turns each bit of numeric value. If the
bit of any numerical value is 001100, then this operator turns these bits into 110011.

Binary Operators:binary operator uses two operands. A binary operator appears with its
operands in the following format Syntax: operand1 operator operand2
Other operators with special formats accept more than two operands. If an operator is given a
null operand, the result is always null. The only operator that does not follow this rule is
concatenation (||).
Arithmetic Operators

Comparison Operators
Logical Operators
Set Operators

Bit-wise Operators
ARITHMETIC OPERATORS:These operators are used to perform operations such as
addition, multiplication, subtraction etc.
 Operator Operation Description

+ Addition Adding values on either side of operator.

- Subtraction Subtract right hand side value to left hand side value.

* Multiplication Multiplies the values present on each side of the operator.

/ Division Divides right hand side value to left hand side value.

% Modulus Divides right hand side value to left hand side value. and returns remainder.

Example: SELECT Emp_salary+2000 as emp_New_salary FROM Employees;

Comparision operator: Used to compare one expression to another

operator operation Description

= Equal to Used to check values of both operands are equal or not if they are
equal returns TRUE
> Greater than If left operand is greater than right operand returns TRUE
< Less than If left operand is less than right operarand returns TRUE
>= Greater than or If left operand is greater than or equal to right operand returns
equal to TRUE
< > or != Not equal to Used to check operands are equal or not.IF not equal return
TRUE.
Example: SELECT * from students where Age = 20;

Logical operators:

Operators Description
All Used to compare specific value with other values in a set
Any Used to compare specific value with any other value is a set
IN Used to compare a specific value to literal values mentioned
BETWEEN Searches for values within range mentioned
And Allows the user to mention multiple condition in where clause
or Combines multiple conditions in where clause
NOT Used to reverse the output of the logical operator
Exists Used to search for the row’s presence in table
like Compares a pattern using wildcard operators
some Similar to any, compares a specific value to some of the values present in a set.

Example: SELECT *from Employee WHERE Salary BETWEEN 20000 AND 50000;
Bitwise Operator: It perform the bit operation on the integer values.To understand this concept
you just knew basics of Boolean algebra.
 1) Bitwise AND(&)
2) Bitwise OR(|)
3) Bitwise NOT operator(~)

Set Operator: A set operator is a keyword that combines the result of two queries into a single
query. Queries containing set operator called compound queries. They are

a. Union
b. Union all
c. Intersect
d. Minus
SYNTAX: Select query SET_OPERATOR Select Another query;

a. Union: The union operation eliminates the duplicate rows from its resultset. This set
operation is applied only when the no.of datatype and column must be same in both
tables.

Syntax: Select query UNION select query;

Example:SELECT dept FROM employee WHERE deptno=10 UNION SELECT dept
FROM employee where deptno = 20;
b. Union All: The union all set operator gives the combined results of all select queries and
includes the duplicate results in the final result.
Syntax: Select query UNION ALL select query;
Example: SELECT dept FROM employee WHERE deptno=10 UNION ALL SELECT
dept FROM employee where deptno = 20;
c. Intersect: It returns only rows that are common to both the queries abd produces an
unduplicated result.

Syntax: Select query INTERSECT select query;

Example: SELECT dept FROM employee WHERE deptno=10 INTERSECT SELECT
dept FROM employee where deptno = 20;
d. Minus: It results the result of the first query but not in the result of the second query.It
produces the unduplicated result .

Syntax: Select query MINUS select query;

Example: SELECT dept FROM employee WHERE deptno=10 MINUS SELECT dept
FROM employee where deptno = 20;
PRECEDENCE OF OPERATORS:
Precedence is the order in which Oracle evaluates different operators in the same expression.
When evaluating an expression containing multiple operators, Oracle evaluates operators with
higher precedence before evaluating those with lower precedence. Oracle evaluates operators
with equal precedence from left to right within an expression.
The following table lists the levels of precedence among SQL operators from high to low,
Operators listed on the same line have the same precedence.

Operators Operation
+,- Identity, negation
*,/ Multiplication, division
+,-,|| Addition, subtraction,concatenation
=,!=, <,>,<=,>=,IS,NULL,LIKE BETWEEN,IN Comparision
NOT Exponentation,logical negation
AND Conjuction
OR disjunction
Example:1+2*3 (2*3 = 6 = => 6+1= 7)

2.8 INTEGRITY CONSTRAINTS THROUGH CREATING A TABLE AND ALTERING

TABLE
SOL constraints are used to specify rules for the data in a table that restricts the values in that
table. Alegrity constraints ensure that the data remains consistent. 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. The constraints improve the quality and integrity of the data. If there is any violation
between the constraint and the data action, the action is aborted. They can be added to a table of
a view when you create it, or after it's created
The following constraints are commonly used in SQL:

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. This ensures that values in a column (or several columns) match values in another
table's column/s.
FOREIGN KEY - Prevents actions that would destroy links between tables this ensures that
values in a column (or several columns) match values in another table's column/s.
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

We may apply integrity Constraints at the column or table level. The table-level Integrity
constraints pply to the entire table, while the column level constraints are only applied to one
column.

There are two ways to create a constraint:

“When the table is created, as part of the CREATE TABLE (or CREATE VIEW) statement”

“After the table is created, as part of the ALTER TABLE (or ALTER VIEW) statement”
Syntax (using Create Table):
The basic syntax of creating an SQL constraint using the CREATE TABLE command is:

“CREATE TABLE table_name( column_namel data_type(size) constraint_name, column_name2

data_type(size) constraint_name,…….);”
“ALTER TABLE table_name ADD/MODIFY COLUMN column_name data_type(size)
constraint_name;”
NOT NULL CONSTRAINT: The not null constraint tells a column that it can't have any null
values in it. This is also a type of inte constraint. This forces a field to always have a value,
meaning we can't create a new record or change an e one without adding a value to it. A NOT
NULL constraint must be declared inline.

NOT NULL using CREATE TABLE statement:

EX : CREATE TABLE Employee ( eid NUMBER(10),

ename VARCHAR2(200) NOT NULL, --ensure the ename field cannot be null.
salary NUMBER(10), doj DATE, deptid NUMBER(10));

 NOT NULL using ALTER TABLE statement:

EX: ALTER TABLE Student MODIFY Age int NOT NULL; ( or ALTER TABLE employee
MODIFY (eid CONSTRAINT nn_eid NOT NULL); )

UNIQUE CONSTRAINT: Unique constraint implies no duplicate values for the column. This
is also a type of integrity constraint, h's similar to a primary key, but it can only accept one null
value and cannot have duplicate values.“A PRIMARY KEY constraint automatically has a
UNIQUE constraint”, However, we can have many UNIQUE constraints per table, but only
one PRIMARY KEY constraint per table.
UNIQUE CONSTRAINT using CREATE TABLE statement
Ex:CREATE TABLE Student( ID int NOT NULL, FirstName varchar(25), LastName
varchar(25), Age int,

Email Id VARCHAR(50) UNIQUE); --ensures email id should be unique

 UNIQUE CONSTRAINT using ALTER TABLE statement
EX: ALTER TABLE employee ADD CONSTRAINT uc_emp_id UNIQUE (eid);

PRIMARY KEY CONSTRAINT: A primary key is a field in a database table that uniquely
identifies each row/record. This is also one of Integrity Constraint. A table can only have one
primary key, which can be made up of one or more fields. creates a composite key when several
fields are used as a primary key. Primary keys must contain UNIQUE values, and cannot contain
NULL values.

PRIMAR KEY CONSTRAINT using CREATE TABLE statement

Ex :CREATE TABLE students ( stu_id NUMBER(10) PRIMARY KEY,

Fst_name VARCHAR2(200),Last_name VARCHAR2(200),join_date DATE);

--will create a primary key on the student id column with simple keyword primary key

 PRIMAR KEY CONSTRAINT using ALTER TABLE statement

Ex:ALTER TABLE student ADD PRIMARY KEY(ID);

FOREIGN KEY CONSTRAINT: A foreign key constraint is used to create a link between a
column(s) in one table and a priman relates to a record 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. The primary key or unique key referenced by the foreign key must already be
created on the reference table. Both tables (the table that has the primary key and the table that
has the foreign key) must be on the same database.

To create this connection, need to pass a command, which is:

“FOREIGN KEY (column_name_first_table) REFERENCES (column_name_second_table)”

 FOREIGN KEY using CREATE TABLE statement

Ex: CREATE TABLE ORDERS(orderID int NOT NULL PRIMARY KEY, OrderNmber int
NOTNULL,CustomerID int FOREIGN KEY REFERENCES customers(CustomerID));

FOREIGN KEY using ALTER TABLE statement

Ex : ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers
CustomerID);
CHECK CONSTRAINT: The CHECK constraint is used to limit the value range that can be
placed in a column. If we define CHECK constraint on a column it will allow only certain values
for this column. The CHECK constraint allows us to specify a condition that needs to be met to
insert data into a table successfully. A check constraint is a type of constraint that ensures a
column (or several columns) meets a specific condition.

Each row in the table must ensure the condition is true, or is unknown due to a NULL value. A
check constraint can be created as:

Syntax: “Create table <table name> (<column name> <data type> constraint <constraint name>
check (column name with condition));”
 CHECK CONSTRAINT using CREATE TABLE statement

Ex: CREATE TABLE VOTING_LIST(ID int PRIMARY KEY , Name NVARCHAR(50) NOT
NULL, Age int NOT NULL CHECK(Age >=18));
 CHECK CONSTRAINT using ALTER TABLE statement
Ex:ALTER TABLE Voting_List ADD CHECK (Age>=1));
DEFAULT CONSTRAINT: 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.

 DEFAULT CONSTRAINT using CREATE TABLE statement

Ex:CREATE TABLE Customers ( ID int NOT NULL,fst_name VARCHAR(25),Age int,City
VARCHAR(25) DEFAULT ‘Hyderabad’);

 DEFAULT CONSTRAINT using ALTER TABLE statement

Ex: ALTER TABLE Customers MODIFY City DEFAULT 'Hyderabad';

2.11 SINGLE ROW (NUMBER, CHARACTER, DATE AND CONVERSION) FUNCTION

Function are very powerful features of SQL. They can be used to do the following
*Perform calculation on data

*Modify individual data items

*Manipulate ouput for groups of rows

*Format dates and numbers for display

*Convert column datatypes

SQL functions some times take arguments and always return a value.
There are two types of functions in SQL:
1)Single row functions: Single row or Scalar functions return a value for every row that is
processed in a query.

DUAl Table in Oracle

This is the single row and single column dummy table table provided by oracle.This is used to
perform mathematical calculations without using a table.

SQL> Select * from DUAl;

Output: DUMMY

X
There are four types of single row functions:
1) Numeric functions: Numerical functions are used to perform operations on
numbers.These are functions that accept numeric input and return numeric values as
output.

Function name Return value

ABS(x) Absolute Value of the number ‘x’
Ex: Select abs(1) from dual; --returns 1
Select abs(-1) from dual; --return -1
CEIL(x) Integer value that greater than or equal to the number ‘x’
Ex: Select ceil(2.83) from dual; --return 3
Select ceil(-1.6) from dual; --return -1
FLOOR(x) Integer value that less than or equal to the number ‘x’
Ex: Select floor(2.83) from dual; --return 2
Select floor(-1.6) from dual; --return -2
TRUNC(x) Truncates value of number ‘x’ up to ‘y’ decimal places
Ex: Select trunc(125.456,1) from dual; --return 125.4
Select trunc(5.7) from dual; --return 5
ROUND(x) Rounded off value of the number ‘x’ up to the number ‘y’ decimal place
Ex: select round(140.234, 2) from dual; --return 140.23
Select round(5.7) from dual; -- return 6
MOD(n,m) Returns the remainder of n divided by m
Ex: select mod(12,5) from dual; --return 2
Select mod(13,4) from dual; --return 1
POWER(x,y) Returns value of x to the power of y
Ex: select power(2,4) from dual; --return 16
SQRT(n) Returns the sqrt room of n
Ex: select sqrt(64) from dual; --return 8
 2) Character or Text Functions: These are functions that accept character input and can
return both character and number values.
Function name Return value
LOWER(string_value) All the letters in string_value is coverted to lower case
Ex: SELECT LOWER(‘HELLO WORLD’); --hello world
UPPER(string_value) All the letters in string_value is coverted to upper case
Ex: SELECT UPPER(‘HELLO WORLD’); --HELLO WORLD
INITCAP(string_value) All the letters in string_value is coverted to mixed case
Ex: SELECT INITCAP(‘HELLO WORLD’); --Hello World
LTRIM(string_value, ‘trim_text’ is removed from the left of ‘string_value’
trim_text) Ex: SELECT LTRIM(‘Good Morning’,’Good’) --Morning
RTRIM(string_value, ‘trim_text’ is removed from the right of ‘string_value’
trim_text) Ex: SELECT RTRIM(‘Good Morning’,’Morning’); --Good
TRIM(trim_text FROM ‘trim_text’from left to right of ‘string_value’,’trim_text’ should be only
string_value) one character long.
Ex:SELECT TRIM(‘o’ FROM ‘Good Morning’); --Gd Mrning
SUBSTR(string_value,m,n) returns ‘n’ no.of characters in’string_value’ starting from ‘m’ position
Ex: SELECT SUBSTR(‘Good Morning’,6,7); --Morning
LENGTH(String_value) Number of characters in ‘string_value’ in returned’
Ex: SELECT LENGTH(‘Good Morning’); --12
LPAD(string_vlue,n, Returns’string_value’ left-padded with ‘pad_value’. The string length
pad_value) will be ‘n’ characters
Ex: SELECT LPAD(‘Good’,6,’*’); --**Good
RPAD(string_vlue,n, Returns’string_value’ right-padded with ‘pad_value’. The string length
pad_value) will be ‘n’ characters
Ex: SELECT RPAD(‘Good’,7,’*’); --***Good
INSTR(string,c) Finds the numeric position of a numeric character
Ex: SELECT INSTR(‘Good Morning’,’M’); --6

3) Data Functions: These are the function s that take values that are of datatypes DATE as
input and return the values of datatype DATE, expect for the MONTHS_BETWEEN
function, which returns a number.
Function name Returns Values
ADD_MONTHS Returns a date value after adding ‘n’ months to the date’x’
(date,n) Ex:SELECT ADD_MONTHS(’27-Aug-25’,3); --27-Nov-25
MONTHS_BETWEEN Returns the no.of months between dates x1,x2
(x1,x2) Ex: SELECT MONTHS_BETWEEN(’27-Aug-25’,’27-Nov-25); --3
ROUND(x, date_format) Returns nearest date ‘x’ nearest century,year, month,date,hour, minute
or seconds as specicified by the ‘date_formate’.
SELECT ROUND(’27-Aug-25’,’DD-MM-YYYY); --1-SEP-25
TRUNC(x,date_format) Returns nearest date ‘x’lesser than or equal to nearest century,year,
month,date,hour, minute or seconds as specicified by the
‘date_formate’.
SELECT TRUNC(’27-Aug-25’,’DD-MM-YYYY); --1-Aug-25
NEXT_DAY(x, week_day) Returns the next_date of the ‘week_day’ on or after the date ‘x’ occurs
SELECT NEXT_DAY(‘27-Aug-25’, wednesday); --3-Sep-25
LAST_DAY(x) It is used to determine no.of days remaining in a month from’x’ date
SELECT LAST_DAY(’27-Aug-25’); --31-Aug-25
SYSDATE Returns the system current date and time
27-Aug-25 7:17:50
NEW_TIME(x,Zone1,Zone2) Returns date and time in Zone2 if date ‘x’ represents the time in Zone1
SELECT NEW_TIME(‘27-Aug-25’,’IST’,’EST’); --27-Aug-25
4) Conversion Functions: These are the functions that help us convert a value in one form
to an other form.
For Example: a null values into a actual value or a value from one datatype to another
datatype like NVL, TO_CHAR, TO_NUMBER, TO_NUMBER etc.
More than one function can be combined together in an expresion.This is knows as
nesting of function.

Function Name Return value

TO_CHAR(x[,y]) Converts Numeric and date values to chararacter string values.it can’t be used for
calculation since it becomes string value
TO_CHAR(3000,$9999) --$3000
TO_CHAR(SYSDATE,’Day,Month YYYY) --Friday,Aug 2025
TO_DATE(x[, Converts valid Numeric and Character values to Date value.Date is formatted to
date fromat]) the format specified by ‘date_format’
TO_DATE(’27-Aug-25’) --27-Aug-25
NVL(x,y) If ‘x’ is NULL, replace it with ‘y’. ‘x’ and ‘y’ must be same datatype
NVL(null,1) --1
DECODE(a,b,c,d,e, Checks the value of ‘a’, if a=b,then returns‘c’. if a=d, then returns’e’.Else returns
default_value) default_value
DECODE(grade,'A','Excellent','B','Good','Average'); --grade=A returns Excellent
2.12 GROUP FUNCTIONS AND AGGREGATION FUNCTIONS:
These functions group the rows of data based on the values returned by the query, These
functions can The used for calculations on groups of selected records. The group functions are
used to calculate aggregate alues like total or average, which return just one total or one average
value after processing a group of rows. Group functions can appear in select lists, GROUP BY
and/or HAVING clauses. They are,

EMPNO EMPNAME JOB SAL DEPTNO

1643720 RAHUL CLERK 20000 20
1643725 DEVI SALESMAN 25000 30
1643730 ANIL SALESMAN 30000 30
1643740 ARUNA MANAGER 32000 20
1643780 BINDU ANALYST 32000 10
1643726 PRANAY CLERK 21000 40
1643750 MANI ANALYST 31000 20
A. COUNT:
1.The count function is used to tally (count) the number of non-null values of an attribute.
2.Count can be used in conjunction(combination) with the distinct clause.
SQL> SELECT COUNT(EMPNO) FROM EMP;

COUNT(EMPNO)
7

SOL> SELECT COUNT(*) FROM EMP WHERE SAL <=25000;

COUNT(*)

2
Count(*) returns the number of total rows returned by the query, including the rows that contain
nulls.

B. MAX:
1.The aggregate function Max (column) can be used only in the column list of a select statement.

2. It returns a minimum value of a expression.

SQL> SELECT MAX (SAL) FROM EMP;

MAX (SAL)
32000
C. MIN:
1. The aggregate function Min (column) can be used only in the column list of a select statement.

2. It returns min value of a given expression

SQL> SELECT MIN (SAL) FROM EMP;

MIN(SAL)
20000

D. SUM:
The sum function computes the total sum fro any specified attribute, using whatever condition.

SQL> SELECT SUM (SAL) FROM EMP;

SUM(SAL)

159000

E. AVG:
The avg function format is similar to that of min and max. it returns an average value o

SQL> SELECT AVG(SAL) FROM EMP;

AVG(SAL)

2714.285

2.14 SUB QUERIES WITH EXAMPLES:

The SOL sub query also called an inner query, nested query is a query nested
within another query example, the SOL Server sub query can nest inside SELECT, INSERT.
DELETE, and UPDATE statements/D inside another.
The outer query can contain the SELECT. INSERT, UPDATE and DELETE
statements. We can the sub query as a column expression, as a condition in SOL clauses, and
Subquery can be used in many place such as:
With the IN or NOT IN BETWEEN operator

With comparison operators like ",>,<,>=,<=

With the EXISTS or NOT EXISTS operator
With the ANY or ALL operator
Rules to be followed Following are the rules to be followed while writing subqueries -
Subqueries must be enclosed within parentheses.

Subqueries can be nested within another subquery.

A subquery must contain the SELECT query and the FROM clause always.

A subquery consists of all the clauses an ordinary SELECT clause can contain: GROUP BY,
WHERE,
HAVING, DISTINCT, TOP/LIMIT, etc. A subquery can return a single value, a single row, a
single column, or a whole table. They are called scalar subqueries.
SUBQUERIES WITH THE SELECT STATEMENT:Subqueries are most frequently used
with the SELECT statement.

Syntax:
SELECT column_name [, column_name] FROM tablel [, table2 ] WHERE column_name
OPERATOR (SELECT column_name [column_name ] FROM table1 [, table2] [WHERE]);

Consider the following employees and departments tables from the sample database:
Employee Table

Department Table
Suppose to find all employees who locate in the location with the id 1500.
SQL>SELECT Employee ID, Employee Name, FROM employees WHERE Department

IN (SELECT Department FROM departments WHERE deptname= ‘IT’);

The query placed within the parentheses is called a subquery. It is also known as an inner query
or inner select. The query that contains the subquery is called an outer query or an outer select.

SUBQUERIES WITH THE SELECT STATEMENT: We can also the subqueries along with
the INSERT statements. The data returned by the subqueries inserted into another table.

INSERT INTO table_name[(column1 [,colums2]) ]

SELECT *column] [column2] FROM tablel [, table2]
[WHERE VALUE OPERATOR]

Example:The following example, we have another emp2 table created with same employees
schema

Empid Ename Salary Deptno New to copy the complete records of employees
table into emp2 table, we can use the following
query.
SQL>INSERT INTO emp2 SELECT FROM employees WHERE ID IN (SELECT ID FROM
employees);

SUBQUERIES WITH THE UPDATE STATEMENT:

A subquery can also be used with the UPDATE statement. It can update single or multiple
columns in a table using a subquery.

Syntax: UPDATE table SET column_name = new_value [WHERE OPERATOR [VALUE]

(SELECT COLUMN_NAME FROM TABLE_NAME [WHERE]):
Example: We have the employees table available which is backup of table. The following
example updates SALARY by 0.25 times in the employees table for all the employees whose
salary is less than or equal to 85000.
SQL>UPDATE employees SET salary=salary * 0.25 WHERE salary IN (SELECT salary FROM
employee WHERE salary =85000);
2.15 JOINS (EQUI JOIN, NON-EQUI JOINS. INNER JOIN, OUTER JOIN CROSS JOIN
AND SELF JOIN) WITH SYNTAX AND EXAMPLES
SQL Joins are used to combine records from multiple tables in the given database. Thus, a JOIN
is a means of combining fields from a couple of tables using values that are common to each of
these.
Join operation combines two relations to create a new relation.

The tables should be joined based on a common column.

The common column should be compatible in terms of domain.

It allows information to be combined from two or more tables.

Join is the real power behind the relation database, allowing the use ofindependent table linked
by common attributes (Characteristics).

Types of SQL.Joins
1)Inner Join

2)Outer Join Left Join/Left Outer Join

Right Join/Right Outer Join
3)Full Join

4)Self join
5)Cross Join/Cartesian Join

1) INNER JOIN (simple join): It is the most common type of SQL join, INNER JOIN selects
records that have matching values in bot tables as long as the condition is satisfied. It returns the
combination of all rows from both the tables where the condition satisfies.

Syntax:SELECT columns FROM tablel INNER JOIN table2 ON tablel.column=table2.column;

this visual diagram, the SOL INNER JOIN returns the shaded area:
Ex: SELECT customers.customer_id, orders.order_id, orders.order_date

FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id

ORDER BY customers.customer_id;

2) LEFT OUTER JOIN: The left join returns all the values from left table and the matching
values from the right table. If there in no matching join value, it will return NULL.
Syntax:The syntax for the LEFT OUTER JOIN in SQL is:
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column table2.column;

In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.
In the below visual diagram, the SQL LEFT OUTER JOIN returns the shaded area.

Example
Now let's look at an example that shows how to use the LEFT OUTER JOIN in a SELECT
statement.
SQL>SELECT customers.customer id, orders.order id, orders.order_date FROM customers
LEFT OUTER JOIN orders ON customers.customer id=orders.customer id ORDER BY
customers.customer id;

3)RIGHT OUTER JOIN:Another type of join is called a RIGHT OUTER JOIN. This type of
join returns RIGHT JOIN returns all the values from the values from the rows of right table and
the matched values from the left table. If there is no matching in both tables, it will return NULL.

Syntax
The syntax for the RIGHT OUTER JOIN in SQL is:
SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON tablel.column =
table2.column;

In some databases, the OUTER keyword is omitted and written simply as RIGHT JOIN.
The RIGHT OUTER JOIN would return the all records from table2 and only those records from
tablel that intersect with table2.

Example:Now let's look at an example that shows how to use the RIGHT OUTER JOIN in a
SELECT statement,
SQL>SELECT customers.customer_id, orders,order_id, orders.order_date FROM customers

RIGHT OUTER JOIN orders ON customers.customer_id=orders.customer_id ORDER BY

customers.customer_id;
4)FULL OUTER JOIN: Another type of join is called a FULL OUTER JOIN. This type of join
returns all rows from the LEFT-and table and RIGHT-hand table with NULL values in place
where the join condition is not met.
The syntax for the SOL FULL OUTER JOIN is:
Syntax: SELECT columns FROM table1 FULL [OUTER] JOIN table2 ON table1.column
table2.column;
It some databases, the OUTER keyword is omitted and written simply as FULL JOIN.
In this visual diagram, the SOL FULL OUTER JOIN returns the shaded area:

Example: Let's look at an example that shows how to use the FULL OUTER JOIN in a SELECT
statement.

SQL>SELECT customers.customer_id, orders.order_id, orders.order_date FROM customers

FULL OUTER JOIN orders ON customers.customer_id= orders.customer_id ORDER BY
customers.customer_id;

5)CARTESIAN JOIN: The CARTESIAN JOIN is also known as CROSS JOIN. In a

CARTESIAN JOIN there is a join for loss each row of one table to every row of another table.
This usually happens when the matching column or WHERE condition is not specified.
In the absence of a WHERE condition the CARTESIAN JOIN will behave like
a CARTESIAN PRODUCT i.e., the number of rows in the result-set is the product of the number
of rows of the two tables.
In the presence of WHERE condition this JOIN will function like a INNER JOIN.
Syntax: SELECT table1.column1,tablel.column2, table2.column1... FROM tablel CROSS JOIN
table2;
Example: SELECT student.name, student.age, course.course_id FROM Student CROSS JOIN
course;

6)SELF JOIN: As the name signifies, in SELF JOIN a table is joined to itself. That is, each row
of the table is yinel with itself and all other rows depending on some conditions. In other words
we can say that it is a join between two copies of the same table.

Syntax:
SELECT a.coulmn1, b.column2 FROM table_name a, table_name b WHERE some_condition;
table name: Name of the table.
some_condition: Condition for selecting the rows.
SQL>SELECT employee.Id, employee.Name, employee.Managerld, manager. Name as
ManagerName FROM Employees employee JOIN Employees manager ON
employee.Managerld manager.Id;
this query, the Employees table is joined with itself and has two different roles:

Role 1: It stores the employce data (alias employee).

Role 2: It stores the manager data (alias manager).

7)EQUI JOIN: EQUI JOIN creates a JOIN for equality or matching column(s) values of the
relative tables.An Equi Join aajoin operation in SQL that combines two tables based on a
matching column between them. Simply put, an Equi Join returns all rows from both tables
where the values in the specified columns are equal.
Syntax: SELECT FROM table1 JOIN table2 ON tablel.column_name table2.column_name;

(OR) SELECT column_name(s) FROM tablel, table2 WHERE table1.column_name =

table2.column_name;
Here tablel and table2 are the two tables being joined, and column_name is the column's name
common in both tables. The ON keyword specifies the condition for combining the tables; the
equality between the values in the given column.
Table name_Record: In this table, we have ld, class and city are the fields.
SQL>SELECT student.name, student.id, record.class, record.city FROM student JOIN record

ON student.city=record.city;
7)NON EOULJOIN: NON EQUI JOIN performs a JOIN using comparison operator other than
equal=-) sign like >,<,>=,<= with conditions,
Syntax: SELECT FROM table namel, table name2
HERE table_namel.column [> | < | >= |<=] table_name2.column;

Example:SQL>SELECT student.name, record.id, record.city FROM student, record WHERE

Student.id < Record.id;