DBMS - unit 2

Oracle training at Goldman Sachs, Banglore. 

 

DATABASE MANAGEMENT SYSTEMS

 

 

UNIT II: Relational Model & Basic SQL

 s:

Relational Model: Introduction to relational model, concepts of domain, attribute, tuple, relation, importance of null values, constraints (Domain, Key constraints, integrity constraints) and their importance

BASIC SQL: Simple Database schema, data types, table definitions (create, alter), different DML operations (insert, delete, update), basic SQL querying (select and project) using where clause, arithmetic & logical operations, SQL functions (Date and Time, Numeric, String conversion).

 

Objectives:

After studying this unit, you will be able to:

l    Describe Relational model

l    Describe SQL and data types

l    Explain the basic structures of SQL queries

l    Know how to create tables

l    Realise aggregate functions and null values

 

2.1   Introduction

Relational Model was proposed by E.F Codd to model data in the form of relations or tables. After designing the conceptual model of database using ER diagram, we need to convert the conceptual model in the relational model which can be implemented using any RDBMS (Relational Data Base Management System) like SQL, MY SQL etc.

The relational model is very simple and elegant; a database is a collection of one or more relations, where each relation is a table with rows and columns.

This simple tabular representation enables even new users to understand the contents of a database, and it permits the use of simple, high-level languages to query the data.

2.2   Relational Model

Relational Model represents how date is stored in relational databases. A Relational database stores data in the form of relations (tables).

Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE and AGE as shown in table.

ROLL_NO	NAME	ADDRESS	PHONE	AGE
1	Nishma	Hyderabad	9455123451	28
2	Sai	Guntur	9652431843	27
3	Swetha	Nellore	9156253131	26
4	Raji	Ongole	9215635311	25

Attribute: Attributes are the properties that define a relation. Ex: ROLL_NO, NAME

Tuple: Each row in a relation is known as tuple.

Ex:

1

Nishma

Hyderabad

9455123451

28

Degree: The number of attributes in the relation is known as degree.

Ex: The degree of the given STUDENT table is 5.

Column: Column represent the set of values for a particular attribute. The column ROLL_NO is extracted from the relation STUDENT.

Ex:

 

ROLL_NO

1

 

Null values: The value which is not known or unavailable is called NULL VALUE. It is represented by blank space.

Cardinality: The number of tuples are present in the relation is called as its cardinality.

Ex: The Cardinality of the STUDENT table is 4.

 

2.3     Concept Of Domain

The domain of a database is the set of all allowable values (or) attributes of the database.

Ex: Gender (Male, Female, Others).

Relation

Ø  A relation is defined as a set of tuples and attributes.

Ø  A relation consists of Relation schema and relation instance.

Ø  Relation schema: A relation schema represents the name of the relation with its attributes.

Ex: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is Relation schema for  STUDENT.

Ø  Relation instance: The set of tuples of a relation at a particular instance of a time is called Relation Instance.

An instance of „Employee „relation

 

Emp_code	Emp_Name	Dept_Name
01234	John	HR
12567	Smith	Sales
21678	Sai	Production
12456	Jay	Design

2.4       Importance of Null values:

Ø  SQL supports a special value known as NULL which is used to represent the values of attributes that may be unknown or not apply to a tuple.

Ø  For example, the apartment_number attribute of an address applies only to the address that is in apartment buildings and not to other types of residences.

Ø  It is important to understand that a NULL value is different from Zero value.

Ø  A Null value is used to represent a missing value, but that is usually has one of the following interpretations:

·         Value unknown (Value exists but it is unknown)

·         Value not available (exists but it is purposely withheld)

·         Attribute not applicable (undefined for this tuple)

Ø  It is often not possible to determine which of the meanings is intended.

 

2.5       Constraints

Ø  On modeling the design of the relational data base, we can put some rules(conditions) like what values are allowed to be inserted in the relation

Ø  Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go in to a table

Ø  This Ensure the accuracy and reliability of the data in the database. Constraints could be either on a column level on a table level.

 

2.6       Domain Constraints In DBMS

Ø  In DBMS table is viewed as a combination of rows and columns

Ø  For example, if you are having a column called month and you want only (jan, feb, march……) as values allowed to be entered for that particular column which is referred to as domain for that particular column

Definition: Domain constraint ensures two things it makes sure that the data value entered for that particular column matches with the data type defined by that column

It    shows    that    the    constraints    (NOT    NULL/UNIQUE/PRIMARY    KEY/FOREIGN KEY/CHECK/DEFAULT)

Domain constraint= data type check for the column +constraints.

Example:, we want to create a table “STUDENT” with “stu_id” field having a value greater than 100, can create a domain and table like this.

§   Create domain id_value int constraint id_test check (value>=100);

§   CREATE table STUDENT (stu_id id value primary key, stu_name varchar (30), stu_age int);

 

2.7       Key constraints in DBMS:

Ø  Constraints are nothing but the rules that are to be followed while entering data into columns of the database table.

Ø  Constraints ensure that the data entered by the user into columns must be within the criteria specified by the condition.

Ø  We have 6 types of key constraints in DBMS

1.       Not Null

2.       Unique

3.       Default

4.       Check

5.       Primary key

6.       Foreign key

 

 

1.       Not Null:

·         Null represents a record where data may be missing data or data for that record may be optional.

·         Once not null is applied to a particular column, you cannot enter null values to that column.

·         A not null constraint cannot be applied at table level.

 

 

Example:

 

Create table EMPLOYEE (id int Not null, name varchar Not null, Age int not null, address char (25), salary decimal (18,2), primary key(id));

 

Ø  In the above example we have applied not null on three columns id, name and age which means whenever a record is entered using insert statement all three columns should contain a value other than null.

Ø  We have two other columns address and salary, where not null is not applied which means that you can leave the row as empty.

 

2.       Unique:

Some times we need to maintain only. Unique data in the column of a database table, this is possible by using a Unique constraint.

Ø  Unique constraint ensures that all values in a column are Unique.

 

Example:

 

Create table PERSONS (id int unique, last_name varchar (25) not null, First name varchar (25), age int);

 

Ø 

In the above example, as we have used unique constraint on ID column we are not supposed to enter the data that is already present, simply no two ID values are same.

 

3.       Default:

Default in SQL is used to add default data to the columns.

 

Ø  When a column is specified as default with same value then all the rows will use the same value i.e., each and every time while entering the data we need not enter that value.

Ø 

But default column value can be customised i.e., it can be over ridden when inserting a data for that row based on the requirement.

(Row with default values “abc”)

 

Example:

 

Create table EMPLOYEE (id int Not null, last_name varchar (25) Not null, first_name varchar (25), Age int, city varchar (25) Default Hyderabad);

 

Ø  As a result, whenever you insert a new row each time you need not enter a value for this  default column that is entering a column value for a default column is optional.

 

4.      Check:

Ø  Check constraint ensures that the data entered by the user for that column is within the range of values or possible values specified.

Example: Create table STUDENT (id int, name varchar (25), age int, check(age>=18));

 

 

Ø  As we have used a check constraint as (age>=18) which means value entered by user for this age column while inserting the data must be less than or equal to 18.

 

5.      Primary Key:

Ø  A primary key is a constraint in a table which uniquely identifies each row record in a database table by enabling one or more column in the table as primary key.

 

Creating a primary key:

Ø  A particular column is made as a primary key column by using the primary key keyword followed by the column name.

Example:

Create table EMP (ID int, name varchar (20), age int, course varchar (10), Primary key (ID));

Ø  Here we have used the primary key on ID column then ID column must contain unique values i.e., one ID cannot be used for another student.

 

6.      Foreign Key:

Ø  The foreign key constraint is a column or list of columns which points to the primary key column of another table.

Ø  The main purpose of the foreign key is only those values are allowed in the present table that will match to the primary key column of another table.

 

From the above two tables, COURSE_ID is a primary key of the table STUDENT_MARKS and also behaves as a foreign key as it is same in STUDENT_DETAILS and STUDENT_MARKS.

 

Example:

 

(Reference Table)

 

Create table CUSTOMER1 (id int, name varchar (25), course varchar (10), primary key (ID));

 

(Child table)

 

Create table CUSTOMER2 (id int, marks int, references customer1(ID));

 

2.8   Integrity Constraints in DBMS:

Ø  There are two types of integrity constraints

1.       Entity Integrity Constraints

2.       Referential Integrity Constraints

 

Entity Integrity constraints:

 

Ø  These constraints are used to ensure the uniqueness of each record or row in the data table.

Ø  Entity Integrity constraints says that no primary key can take NULL VALUE, since using primary key we identify each tuple uniquely in a relation.

Example:

 

Explanation:

Ø  In the above relation, EID is made primary key, and the primary key can‟t take NULL values but in the 3rd tuple, the primary key is NULL, so it is violating Entity integrity constraints.

Referential Integrity constraints:

 

Ø  The referential integrity constraint is specified between two relations or tables and used   to maintain the consistency among the tuples in two relations.

Ø  This constraint is enforced through foreign key, when an attribute in the foreign key of relation R1 have the same domain as primary key of relation R2, then the foreign key of R1 is said to reference or refer to the primary key of relation R2.

Ø  The values of the foreign key in a tuple of relation R1 can either take the values of the primary key for some tuple in Relation R2, or can take NULL values, but can‟t be empty.

 

 

 

 

Explanation:

 

Ø  In the above, DNO of the first relation is the foreign key and DNO in the second relation is the primary key

Ø  DNO=22 in the foreign key of the first relation is not available in the second relation so, since DNO=22 is not defined in the primary key of the second relation therefore Referential integrity constraints is violated here.

 

 

2.9         Basic SQL (introduction)

 

Ø  SQL stands for Structure Query Language it is used for storing and managing data in relational database management system.

Ø  It is standard language for relational database system. It enables a user to create, read, update and delete relational databases and tables.

Ø  All the RDBMS like MYSQL, Oracle, MA access and SQL Server use SQL as their standard database language.

Ø  SQL allows users to Query the database in a number of ways using statements like common English.

Rules: SQL follows following rules

·         SQL is not a case sensitive. Generally, keywords are represented in UPPERCASE.

·         Using the SQL statements, you can perform most of the actions in a database.

·         Statements of SQL are dependent on text lines. We can use a single SQL statement on one or multiple text line.

 

2.10   SQL Process:

Ø  When an SQL command is executing for any RDBMS, then the system figure out the best way to carry out the request and the sql engine determines that how to interrupt the task.

Ø  In the process, various components are included. These components can be optimization engine, query engine, query dispatcher etc.,

Ø  All the non-sql queries are handled by the classic query engine, but sql query engine won‟t handle logical files.

2.11   Characteristics of SQL:

·         SQL is easy to learn.

·         SQL is used to access data from relational database management system.

·         SQL is used to describe the data.

·         SQL is used to create and drop the database and table.

·         SQL allows users to set permissions on tables, procedures and views.

2.12   Simple database Schema:

Ø  A database schema is a structure that represents the logical storage of the data in the database.

Ø  It represents the organization of data and provides information about the relationships between the tables in a given database.

Ø  A database schema is the logical representation of a database, which shows how the data is stored logically in the entire database.

Ø  It contains list of attributes and instruction that informs the database engine that how the data is organized and how the elements are related to each other.

Ø  A database schema contains schema objects that may include tables, fields, packages, views, relationship, primary key, foreign key.

Ø  In actual, the data is physically stored in files that may be in unstructured form, but to retrieve it and use it, we need to keep them in a structured manner. To do this a database schema is used. It provides knowledge about how the data is organized in a database and how it is associated with other data.

Ø  A database schema object includes the following:

·         Consistent formatting for all data entries.

·         Database objects and unique keys for all data entries.

·         Tables with multiple columns, and each column contains its names and datatypes.

·         The given diagram is an example of a database schema it contains three tables, their data types. This also represents the relationships between the tables and primary keys as well as foreign keys.

 

2.13   SQL Commands:

SQL commands are categorized into three types.

 

1.       Data Definition Language (DDL): used to create (define) a table.

 

2.       Data Manipulation Language (DML): used to update, store and retrieve data from tables.

 

3.       Data Control Language (DCL): used to control the access of database created using DDL and DML.

 

2.14   SQL DATATYPES :

SQL data type is used to define the values that a column can contain

 

Every column is required to have a name and data type in the database table. DATA TYPES OF SQL :

 

Binary data type

Numeric data type

Extract numeric data type

String data type

Date data type

 

 

1.      BINARY DATATYPES:

There are three types of binary data types which are given below

 

DATA TYPE	DESCRIPTION
Binary	It has a maximum length of 800 bytes. It contains a fixed- length binary data
Var binary	It has a maximum length of 800 bytes. It contains a variable - length binary data
Image	It has a maximum length of 2,147,483,647 bytes. It contains a variable - length binary data

 

 

2.      NUMERIC DATATYPE:

 

DATA TYPE	FROM	TO	DESCRIPTION
Float	-1.79 E +308	1.79 E +308	It is used to specify a floating-point value. Ex: 6.2, 2.9 etc
Real	-3.40 E +38	3.40 E +38	It specifies a single precision floating point number.

 

3.      EXACT NUMERIC DATA TYPE:

 

DATA TYPE	DESCCRIPTION
Int	It is used to specify an integer value
Small int	It is used to specify small integer value
Bit	It has the number of bits to store
Decimal	It specifies a numeric value that can have a decimal number
Numeric	It is used to specify a numeric value

 

4.      DATE AND TIME DATATYPES:

 

DATA TYPE	DESCRIPTION
Date	It is used to store the year, month, and days value
Time	It is used to store the hour, minute, and seconds value
Time stamp	It stores the year, month, hour, minute, and the second value

 

5.      STRING DATATYPE:

 

DATA TYPE	DESCRIPTION
Char	It has a maximum length of 8000 characters. It contains fixed-length non- Unicode characters.
Varchar	It has a maximum length of 8000 characters. It contains variable-length non-Unicode characters.
Text	It has a maximum length of 2,147,483,647 characters. It contains variable- length non-Unicode characters.

 

2.15       TABLE DEFINITIONS: (CREATE, ALTER)

SQL TABLE: SQL table is a collection of data which is organized in terms of rows and columns.

 

·         In DBMS, the table is known as relation and row as a tuple

·        Let‟s see an example of the “EMPLOYEE “table

 

EMP_ID	EMP_NAME	CITY	PHONE_ID
1	Kristen	Washington	7289201223
2	Anna	Franklin	9378282882
3	Jackson	California	9264783838
4	Daniel	Hawaii	9638482678

·         In the above table, “EMPLOYEE” is the table name, “EMP_ID, “EMP_NAME”, “CITY”,” PHONE-NO” are the column names.

·          The combination of data of multiple columns forms a row

EG: 1, “Kristen”, “Washington” and “7289201223 “are the data of one row

 

 

2.16   OPERATIONS ON TABLE:

1.       Create table

2.       Alter table

3.       Drop table

1. Create table: SQL create table is used to create a table in the database. To define the table, you should define the name of the table and also define its column and column‟s data type.

SYNTAX:

 

Create table table_name (“column1” “datatype”,

 

“column2” “datatype”, “column3” “datatype”,

….

 

“column N” “datatype”);

 

EXAMPLE:

 

SQL > create table employee (emp_id int, emp_name varchar (25), phone_no int, address char (30));

·         If you create the table successfully, you can verify the table by looking at the message by the sql server. else you can use DESC command as follows

SQL > DESC employee;

 

FIELD	TYPE	NULL	DEFAULT	EXTRA
Emp_id	Int (11)	No	NULL
Emp_name	Varchar (25)	No	NULL
Phone_no	No	Int (11)	NULL
address	yes		NULL	Char(30)

 

2.  ALTER TABLE:

·         The alter table command adds, delete or modifies columns in a table

·         The alter table command also adds and deletes various constraints in a table

·         The following SQL adds an “EMAIL” column to the “EMPLOYEE “table

SYNTAX:

 

ALTER table table_name add column1 datatype;

 

EXAMPLE:

 

ALTER table employee add email varchar (255);

 

SQL > DESC employee;

 

FIELD	TYPE	NULL	DEFAULT	EXTRA
Emp_id	Int (11)	No	NULL
Emp_name	Varchar (25)	No	NULL
Phone_no	No	Int (11)	NULL
Address	Yes		NULL	Char (30)
Email	Varchar (255)		NULL

 

 

3.    DROP TABLE:

 

·         The drop table command deletes a table in the data base

·         The following example SQL deletes the table “EMPLOYEE”

SYNTAX :

 

DROP table table_name;

 

EXAMPLE:

 

DROP table employee;

 

·         Dropping a table results in loss of all information stored in the table.

2.17           Different DML Operations (insert, delete, update):

Ø  DML-Data Manipulation Language.

Ø  Data Manipulation Commands are used to manipulate data to the database.

Ø  Some of the data manipulation commands are

1.       Insert

2.       Update

3.       Delete

 

 

1.    Insert:

SQL insert statement is a sql query. It is used to insert a single multiple records in a table.

 

Syntax:

 

Insert into table name values (value 1, value 2, value 3);

 

Let‟s take an example of table which has 3 records within it.

 

▪        insert into student values(„alekhya‟,501,‟hyderabad‟);

▪        insert into student values(„deepti‟,502,‟guntur‟);

▪        insert into student values(„ramya‟,503,‟nellore‟); The following table will be as follows:

 

NAME	ID	CITY
Alekhya	501	Hyderabad
Deepti	502	Guntur
Ramya	503	Nellore

 

2.  Update:

Ø  The SQL Commands update are used to modify the data that is already in the database.

Ø  SQL Update statement is used to change the data of records held by tables which rows is to be update, it is decided by condition to specify condition, we use “WHERE” clause.

Ø  The update statement can be written in following form:

Syntax:

Update table_name set column_name=expression where condition;

 

Example:

 

Let‟s take an example: here we are going to update an entry in the table.

 

Update students set name=‟rasi‟ where id=503;

 

After update the table is as follows:

 

NAME	ID	CITY
Alekhya	501	Hyderabad
Deepti	502	Guntur
Rasi	503	Nellore

 

3.    Delete:

Ø  The SQL delete statement is used to delete rows from a table.

Ø  Generally, delete statement removes one or more records from a table.

Syntax:

 

delete from table_name [where condition];

 

Example:

 

Let us take a table named “student” table

 

Delete from students where id=501;

 

Resulting table after the query:

 

NAME	ID	CITY
Deepti	502	Guntur
Rasi	503	Nellore

 

 

2.18   Basic SQL querying (select and project) using where clause:

Ø  The following are the various SQL clauses:

SQL Clause

 

Group by clause        having clause                         Order by clause

 

1.      Group by:

Ø  SQL group by statement is used to arrange identical data into groups.

Ø  The group by statement is used with the SQL select statement.

Ø  The group by statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

 

Syntax:

Select  column from table_name where column group by column, order by column;

 

 

Sample table: product

PRODUCT	COMPANY	QTY	RATE	COST
Item 1	Com 1	2	10	20
Item 2	Com 2	3	25	75
Item 3	Com 1	2	30	60
Item 4	Com 3	5	10	50
Item 5	Com 2	2	20	40

 

Example:

Ø  Select company count (*) from product group by company;

 

Output:

Com 1	2
Com 2	3
Com 3	5

 

2.      Having clause:

Ø  Having clause is used to specify a search condition for a group or an aggregate.

Having clause is used in a group by clause, if you are not using group by clause then you can use              having function like a where clause.

 

Syntax:

Select column1, column2 from table_name

 

Where conditions

 

Group by column1, column2 Having conditions

Order by column1, column2;

 

 

Example:

Ø  select company count (*) from product

Group by company Having count (*) > 2;

 

Output:

Com 3	5
Com 2	2

 

 

 

3.      Order by clause:

The order by clause sorts the result _set in ascending or descending order.

 

Syntax:

 

Select column1, column2, from table_name

 

Where condition

 

Order by column1, column2…asc;

 

Sample table:

 

Take a student table

 

Example:

 

Select * from student order by name;

 

Output:

 

NAME	ID	CITY
Alekhya	501	Hyderabad
Deepti	502	Guntur
Rasi	503	Nellore

 

 

2.19       SQL Where clause:

Ø  A where clause in SQL is a data manipulation language statement.

Ø  Where clauses are not mandatory clauses of SQL DML statements but it can be used to limit the number of rows affected by a SQL DML statement or returned by query.

Ø  Actually, it follows the records.it returns only those queries which the specific conditions.

 

Syntax:

Select column1, column2, …………column from table_name where[condition];

 

Ø  Where clause uses same conditional selection.

 

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

 

 

 

 

 

 

2.20   Arithmetic and logical operations:

 

SQL operators:

Ø  SQL statements generally contain some reserved words or characters that are used to perform operations such as arithmetic and logical operations etc. Their reserved words are known as operators.

SQL arithmetic operator:

Ø  We can use various arithmetic operators on the data stored in tables.

Ø  Arithmetic operators are:

 

+	Addition
-	Subtraction
/	Division
*	Multiplication

 

%

modulus

 

1.       Addition (+):

It is used to perform addition operation on data items.

 

Sample table:

	EMP_ID	EMP_NAME	SALARY
	1	Alex	25000
	2	John	55000
	3	Daniel	52000
	4	Sam	12312

 

 

Ø  select emp id, emp_name, salary, salary+100 as “salary +100” from

addition;

Output:

	EMP_ID	EMP_NAME	SALARY	SALARY+100
	1	Alex	25000	25100
	2	John	55000	55100
	3	Daniel	52000	52100
	4	Sam	12312	12412

Ø  Here we have done addition of 100 to each emp‟s salary.

2.       Subtraction (-):

Ø  It is used to perform subtraction on the data items.

Example:

Select emp_id, emp_name, salary, salary-100 as “salary-100” from

subtraction;

EMP_ID	EMP_NAME	SALARY	SALARY-100
1	Alex	25000	24900
2	John	55000	54900
3	Daniel	52000	51900
4	Sam	90000	89900

Here we have done subtraction of 100 for each emp‟s salary.

 

3.      Division (/):

Ø  The division function is used to integer division (x is divided by y).an integer value is returned.

Example:

Ø  Select emp_id, emp_name, salary, salary/100 as “salary/100” from division;

 

EMP_ID	EMP_NAME	SALARY	Salary/100
1	Alex	25000	250
2	John	55000	550
3	Daniel	52000	520
4	Sam	90000	900

 

4.      Multiplication (*):

Ø  It is used to perform multiplication of data items.

Ø  Select emp_id, emp_name, salary, salary*100 as “salary*100” from multiplication;

 

EMP_ID	EMP_NAME	SALARY	SALARY*100
1	Alex	25000	2,500,000
2	John	55000	5,500,000
3	Daniel	52000	5,200,000
4	Sam	90000	9,000,000

Ø  Here we have done multiplication of 100 to each emp‟s salary.

5.     Modulus (%):

Ø  It is used to get remainder when one data is divided by another.

Ø  Select emp_id, emp_name, salary, salary%25000 as “salary%25000” from modulus;

Output:

	EMP_ID	EMP_NAME	SALARY	SALARY%25000
	1	Alex	25000	0
	2	John	55000	5000
	3	Daniel	52000	2000
	4	Sam	90000	15000

Ø  Here we have done modulus operation to each emp‟s salary.

2.21       Logical operations:

Ø  Logical operations allow you to test for the truth of a condition.

Ø  The following table illustrates the SQL logical operator.

OPERATOR	MEANING
ALL	Returns true if all comparisons are true
AND	Returns true if both expressions are true
ANY	Returns true if any one of the comparisons is true
BETWEEN	Return true if the operand is within a range
IN	Return true if the operand is equal to one of the values in a list
EXISTS	Return true if the sub query contains any rows

1.      AND:

The AND operator allows you to construct multiple condition in the WHERE clause of an SQL statement such as select.

Ø  The following example finds all employees where salaries are greater than the 5000 and less than 7000.

Ø  Select first_name, last_name, salary from employees where

salary>5000 AND salary<7000 order by salary;

Output:

FIRST_NAME	LAST_NAME	SALARY
John	Wesley	6000
Eden	Daniel	6000
Luis	Popp	6900
Shanta	Suji	6500

 

 

2.      ALL:

The ALL operator compares a value to all values in another value set.

 

Ø  The following example finds all employees whose salaries are greater than all salaries of employees.

EX:

select first_name, last_name, salary from employees where salary>=ALL (select salary from employees where department_id =8) order by salary DESC;

Output:

FIRST_NAME	LAST_NAME	SALARY
Steven	King	24000
John	Russel	17000
Neena	Kochhar	14000

 

3.      ANY:

The ANY operator compares a value to any value in a set ascending to condition.

The following example statement finds all employees whose salaries are greater than the average salary of every department.

EX:

select first_name, last_name, salary from employees where salary >ANY (select avg (salary) from employees‟ group by department_id) order by first_name, last_name;

 

Output:

FIRST_NAME	LAST_NAME	SALARY
Alexander	Hunold	9000.00
Charles	Johnson	6200.00
David	Austin	4800.00
Eden	Flip	9000.00

 

4.      Between:

Ø  The between operator searches for values that are within a set of values.

Ø  For example, the following statement finds all employees where salaries are between 9000 and 12000.

EX:

 

select first_name, last_name, salary from employees where salary between 9000 AND 12000 order by salary;

 

Output:

FIRST_NAME	LAST_NAME	SALARY
Alexander	Hunold	9000.00
Den	Richards	10000.00
Nancy	Prince	12000.00

 

 

5.      IN:

Ø  The IN operator compares a value to list of specified values. The IN operator return true if compared value matches at least one value in the list.

Ø  The following statement finds all employees who work in department _id 8 or 9. EX:

select first_name, last_name, department_id from employees where department_id IN (8,9) order by department_id;

 

Output:

FIRST_NAME	LAST_NAME	DEPARTMENT_ID
John	Russel	8
Jack	Livingstone	8
Steven	King	9
Neena	Kochhar	9

 

6.      Exists:

Ø  The EXISTS operator tests if a sub query contains any rows.

Ø  For example, the following statement finds all employees who have dependents.

Ø  select first_name, last_name from employees where EXISTS (select 1 from dependent d where d.employee_id=e.employee_id);

 

FIRST_NAME	LAST_NAME
Steven	King
Neena	Kochhar
Alexander	Hunold

 

 

2.22       SQL FUNCTIONS (Date & Time, Numeric, Aggregate, String conversions):

 

 

DATE & TIME FUNCTIONS:

 

 

 

Some important date and time functions are below:

 

Sysdate: It generates the system date. Ex: Select sysdate from dual;

Output: 05-DEC-2021.

 

ADD_MONTHS: This function returns a date after adding data with specified no of months. EX: Select ADD_MONTHS („2017-02-29‟,1) from dual;

Output: 31-MAR-17.

Select add_months(sysdate,3) from dual;

 

Output: 05-MAR-22.

 

CURRENT_DATE: This function displays the current date.

 

Ex: Select CURRENT_DATE from dual;

 

Output: 05-DEC-2021.

 

NEXT_DAY: This function represents both day and date and returns the day of the next given day.

 

EX: Select NEXT_DAY(SYSDATE,‟MONDAY‟) from dual;

 

Output: 07-DEC-21.

 

LAST_DAY: This function returns a day corresponding last day of months.

 

EX: Select LAST_DAY (sysdate) from dual;

 

Output: 31-DEC-21.

 

MONTHS_BETWEEN: It is used to find no of months between two given dates.

 

EX: Select MONTHS_BETWEEN(‟16-APRIL-2021‟,‟16-AUGUST-2021) from dual;

 

Output: -4.

 

ROUND: It gives the nearest value or round off value for the argument pass. (or) It returns a date rounded to a specific unit of measure.

EX: Select ROUND(‟26-NOV-21‟,‟YYYY‟) from dual;

 

Output: 01-JAN-22.

 

TRUNC: This function returns the date with the time(co-efficient) portion of the date truncated to the unit specified.

EX: Select TRUNC (sysdate, ‟MM‟) from dual;

Output: 01-DEC-21.

 

TO_DATE: This function converts date which is in the character string to a date value.

 

EX: Select TO_DATE (‟01 jan 2017‟,‟DD MM YYYY‟) from dual;

 

Output: 01-JAN-17.

 

TO_CHAR: This function converts DATE or an INTERVAL value to a character string in a specified format.

EX: Select TO_CHAR (sysdate,‟DD MM YYYY‟) from dual;

 

Output: 05 12 2021.

 

LEAST: This function displays the oldest date present in the argument list.

 

EX: Select LEAST(‟01-march-2021‟,‟16-feb-2021‟,‟28-dec-2021‟) from dual;

 

Output: 01-MAR-21.

 

GREATEST: This function displays the latest date present in the argument list.

EX: Select GREATEST (‟01-march-2021‟,‟16-feb-2021‟,‟28-dec-2021‟) from dual;

 

Output: 28-DEC-21.

 

2.23   Aggregate Functions:

Aggregate Functions take a collection of values as input and returns a single value.

 

1. Count ()

 

2. Sum ()

 

3. Avg ()

 

4. Max ()

 

5. Min ()

 

1.  Count (): This function returns number of rows returned by a query.

 

Syntax: Select count(column_name)

 

From table_name Where condition);

Example: Select count (distinct manager_id) from employees;

 

2.Sum (): It will add/ sum all the column values in the query.

 

Syntax: Select sum (column_name)

 

From table_name Where condition);

Example: Select sum(salaries) from employees;

 

3.Avg (): Avg function used to calculate average values of the set of rows.

 

Syntax: Select avg (column_name)

 

From table_name Where condition);

Example: Select avg(salary) from employees;

 

4.Max (): This function is used to find maximum value from the set of values.

 

Syntax: Select max (column_name)

 

From table_name Where condition);

Example: Select max (salary) from employees;

 

5.Min (): This function is used to find minimum value from the set of values.

 

Syntax: Select min (column_name)

 

From table_name

 

Where condition);

 

Example: Select min (salary) from employees;

 

2.24  

SQL NUMERIC FUNCTIONS:

 

Numeric functions are used to perform operations on numbers and return numbers. Following are some of the Numeric functions

1.       ABS (): It returns the absolute value of a number. EX: select ABS (-243.5) from dual;

OUTPUT: 243.5

 

2.       ACOS (): It returns the cosine of a number. EX: select ACOS (0.25) from dual;

OUTPUT: 1.318116071652818

 

3.       ASIN (): It returns the arc sine of a number. EX: select ASIN (0.25) from dual;

OUTPUT: 0.253680255142

 

4.       CEIL (): It returns the smallest integer value that is a greater than or equal to a number. EX: select CEIL (25.77) from dual;

OUTPUT: 26

 

5.       FLOOR (): It returns the largest integer value that is a less than or equal to a number. EX: select FLOOR (25.75) from dual;

OUTPUT: 25

6.       TRUNCATE (): This does not work for SQL server. It returns the truncated to 2 places right of the decimal point.

EX: select TRUNCATE (7.53635, 2) from dual;

OUTPUT: 7.53

7.       MOD (): It returns the remainder when two numbers are divided. EX: select MOD (55,2) from dual;

OUTPUT: 1.

8.       ROUND (): This function rounds the given value to given number of digits of precision. EX: select ROUND (14.5262,2) from dual;

OUTPUT: 14.53.

9.       POWER (): This function gives the value of m raised to the power of n. EX: select POWER (4,9) from dual;

OUTPUT: 262144.

10.     SQRT (): This function gives the square root of the given value n. EX: Select SQRT (576) from dual;

OUTPUT: 24.

11.     LEAST (): This function returns least integer from given set of integers. EX: select LEAST (1,9,2,4,6,8,22) from dual;

OUTPUT: 1.

 

12.   GREATEST (): This function returns greatest integer from given set of integers. EX: select GREATEST (1,9,2,4,6,8,22) from dual;

OUTPUT: 22

 

 

 

2.25   STRING CONVERSION FUNCTIONS OF SQL:

 

 

 

String Functions are used to perform an operation on input string and return the output string. Following are the string functions

1. CONCAT (): This function is used to add two words (or) strings.

 

EX: select „database‟ ||‟ „|| „management system‟ From dual;

 

OUTPUT: „database management system‟

 

2. INSTR (): This function is used to find the occurrence of an alphabet. EX: instr („database system‟,‟ a‟) from dual; OUTPUT: 2 (the first occurrence of „a‟)

3. LOWER (): This function is used to convert the given string into lowercase. EX: select lower („DATABASE‟) from dual;

OUTPUT: database

 

4. UPPER (): This function is used to convert the lowercase string into uppercase. EX: select upper („database „) from dual;

OUTPUT: DATABASE

 

5. LPAD (): This function is used to make the given string of the  given size by adding the given symbol.  EX: > lpad („system „, 8, „0‟) from dual;

OUTPUT: 00system

 

6. RPAD (): This function is used to make the given string as long as the given size by adding the given symbol on the right.

EX: rpad („system „,8, „0„) from dual;

 

OUTPUT: system00

 

7. LTRIM (): This function is used to cut the given substring from the original string. EX: ltrim („database „, „data „) from dual;

 

OUTPUT: base

 

8. RTRIM (): This function is used to cut the given substring from the original string. EX: rtrim („database „, „base „) from dual;

OUTPUT: data.

 

9.   INITCAP (): This function returns the string with first letter of each word starts with uppercase.

 

EX: Select INITCAP („data base management system‟) from dual;

 

OUTPUT: Data Base Management System.

 

10.   LENGTH (): Tis function returns the length of the given string.

 

EX: select LENGTH („SQ LANGUAGE‟) from dual;

 

OUTPUT: 11.

 

11. SUBSTR (): This function returns a portion of a string beginning at the character position. EX: select SUBSTR („MY WORLD IS AMAZING‟,12,3) from dual;

OUTPUT: AM.

 

12. TRANSLATE (): This function returns a string after replacing some set of characters into another set. EX: select TRANSLATE („Delhi is the capital of India‟,‟i‟,‟a‟) from dual; OUTPUT: Delha as the capatal andaa.

 

 

Review Questions:

1.       Why NULL values are needed in databases?

2.       Discuss GROUPBY and HAVING clauses with an example. And also give the constraints related to their usage.

3.       Consider the SAILOR DATABASE

Sailors (Sid: string, sname: string, rating: integer, age: real) Boats (bid: integer, bname: string, colour: string)

Reserves (Sid: integer, bid: integer, day: date)

Based on the above schema, write the corresponding SQL queries for the following? Find the colours of boats reserved by Lubber.

Find the names of sailors who have reserved at least one boat. Find the names of sailors who have reserved a red or green boat.

Find the names of the sailors who have reserved both a red boat and a green boat.

 

Find names of sailors who have reserved all boats.

4.       Write about the usability of „group by‟ and „having‟ clauses In SQL.

5.       How would you use the operators IN, EXISTS, UNIQUE, ANY and ALL in writing nested queries? Why are they useful? Explain with an example.

6.       Explain commands with respect to SQL: (i) Rename (ii) Alter (iii) View

7.       Explain two aggregate functions of SQL.

8.       Explain the following SQL constructs with examples:

(1)  order by (2) group by and having (3) as select (4) schema

 

9.       Write SQL Queries for following set of tables:

EMPLOYEE (EmpNo, Name, DoB, Address, Gender, Salary, DNumber) DEPARTMENT (DNumber, Dname, ManagerEmpNo, ManagerStartDate). Display the Age of „male‟ employees.

Display all employees in Department named „Marketing‟. Display the name of highest salary paid „female‟ employee. Which employee is oldest manger in company?

Display the name of department of the employee „SMITH.

10.    Describe creating and modifying relations using SQL. Give examples for each.

11.    Explain about integrity constraints over relations.

12.    Consider the following insurance database, where the primary keys are underlined.

Construct the following SQL queries for this relational database. person (driver-id#, name, address)

car (license, model, year)

accident (report-number, date, location) owns (driver-id#, license)

participated (driver-id, car, report-number, damage-amount)

a.       Find the total number of people who owned cars that were involved in accidents in 1989.

b.       Find the number of accidents in which the cars belonging to “John Smith” were involved.

c.        Add a new accident to the database; assume any values for required attributes.

d.       Delete the Mazda belonging to “John Smith”.

e.       Update the damage amount for the car with license number “AABB2000” in the accident with report number “AR2197” to $3000.

13.    List the SQL functions for string conversions.

14.    Write SQL statements for following:

Student (Enrno, name, courseId, emailId, cellno) Course (courseId, course_nm, duration)

 

i.       Add a column city in student table.

ii.       Find out list of students who have enrolled in “computer” course.

iii.       List name of all courses with their duration.

iv.       List name of all students start with „a‟.

v.       List email Id and cell no of all mechanical engineering students.

15.    How to define a domain constraint? Give an example.

16.    What is an integrity constraint? Explain its enforcement by DBMS with illustrative

17.    List the data types supported by SQL.

18.    Demonstrate the use of DISTINCT keyword in SQL select statement.

19.    Consider the following database schema to write nested queries in SQL Supplier (id, name, city)

Parts (pno, pname, pdescription) Supply (id, pno, cost)

i.       Find the names of the parts supplied by “RamRaj”

ii.       Find the names of the suppliers who supply “Nuts”

iii.       Find the cost of bolts being supplied by Nagpur suppliers.

 

 

References:

·         Raghurama Krishnan, Johannes Gehrke, Database Management Systems, 3rd Edition, Tata McGraw Hill.

·         C.J. Date, Introduction to Database Systems, Pearson Education.

·         Elmasri Navrate, Fundamentals of Database Systems, Pearson Education.