Unit One: Fundamental concepts of relational database

1.1.1 File based approach

The file-based approach involves storing data in separate computer files defined by different application
programs, while the database approach involves sharing a pool of related data from a centralized location.
The file-based approach allows for easy editing but has disadvantages of data redundancy and
inconsistency.
Using the file-based system to keep organizational information has a number of disadvantages. Listed
below are five examples.
 Data redundancy
 Security problems
 Data isolation
Data isolation is a property that determines when and how changes made by one operation become visible
to other concurrent users and systems.
 Integrity problems
. It refers to the maintenance and assurance that the data in a database are correct and consistent.
 Concurrency access
Concurrency is the ability of the database to allow multiple users access to the same record without
adversely affecting transaction processing.
1.1.2 Database Approach
The database approach is an improvement on the shared file solution as the use of a database management
system (DBMS) provides facilities for querying, data security and integrity, and allows simultaneous access to
data by a number of different users.
What is a database?
A database is a shared collection of related data used to support the activities of a particular organization.
Database Properties
A database has the following properties:
It is a representation of some aspect of the real world or a collection of data elements (facts) representing
real world information.
A database is logical, coherent and internally consistent.
A database is designed, built and populated with data for a specific purpose.
Each data item is stored in a field. A combination of fields makes up a table.
Characteristics and Benefits of a Database
There are a number of characteristics that distinguish the database approach from the file-based system or
approach.
 Self-describing nature of a database system
A database system is referred to as self-describing because it not only contains the database itself, but also
metadata which defines and describes the data and relationships between tables in the database.
 Insulation between program and data
 Support for multiple views of data
 Sharing of data and multiuser system
 Control of data redundancy
 Data sharing
 Enforcement of integrity constraints
 Restriction of unauthorized access
 Data independence
 Transaction processing
 Backup and recovery facilities
Relational Database Management System
A DBMS is a collection of programs enabling users to create, maintain, and control access to databases. Its
primary goal is to provide a convenient and efficient environment for users to retrieve and store
information.
A relational database comprises one or more relations, essentially tables. Keys, such as primary keys,
uniquely identify records. Foreign keys establish relationships between tables.
Primary Keys:
Primary keys uniquely identify each row and cannot have duplicate or NULL values. A table typically has
one primary key, which may be a composite key if multiple fields are involved.
Foreign Keys:
Foreign keys link tables, referencing primary keys from another table. They establish relationships
between tables, contributing to data integrity and consistency.
Requirement for Installing MS SQL Server DBMS Software
The following system requirements cover SQL Server 2012 Standard Edition on 32-bit and x64 platforms,
as well as Itanium-based systems.
SQL Server 2012 64-bit Hardware Requirements:
Component Minimum Requirement Recommended Requirement
Processor x64Processorwithatleast1.4GHz 2.0GHzorfaster,4or more cores

RAM(64-bit) 1GBforExpress,2GBforall others 4GBormore

Hard Disk Space (Database 3.5GBminimum,10GBormore 40GBormore

Engine) recommended
Hard Disk Space (Analysis 250MBminimum,2GBormore 40GBormore
Services) recommended
Hard Disk Space(Reporting 3GBminimum,10GBormore 40GBormore
Services) recommended
Hard Disk Space(Integration 1.5GBminimum,2.5GBormore 40GBormore
Services) recommended
Hard Disk Space (Full 3.8GBminimum,10GBormore 40GBormore
Installation) recommended

SQL Server2012 64-bit Software Requirements:

 Operating System:
 .NET Framework:
 SQL Server 2012 Edition:
 SQL Server 2012 Service Pack:
SQL Server 2012 32-bit Hardware Requirements:

Component Minimum Requirement Recommended

Requirement

Processor x86orx64Processorwithatleast 2.0GHzorfaster,4or more

1.4GHz cores

RAM(32-bit) 1GBforExpress,2GBforallothers 4GBormore

Hard Disk Space(Database Engine)

2.2GBminimum,10GBormore 40GBormore
recommended

Hard Disk Space(Analysis 250MBminimum,2GBormore 40GBormore

Services) recommended

Hard Disk Space(Reporting 2.1GBminimum,10GBormore 40GBormore

Services) recommended

Hard Disk Space(Integration 1.5GBminimum,2.5GBormore 40GBormore

Services) recommended

Hard Disk Space(Full Installation)2.5GBminimum,10GBormore 40GBormore

recommended

Structured query language

SQL is a flexible language that you can use in a variety of ways. It‘s the most widely used tool for
communicating with a relational database. SQL is a standard database language specifically designed for
storing, retrieving, managing or manipulating the data inside a relational database management system
SQL is the most widely-implemented database language and supported by the popular relational database
systems, like MySQL, SQL Server, and Oracle.
There are lots more things you can do with SQL:
 You can create a database.
 You can create tables in a database.
 You can query or request information from a database.
 You can insert records in a database.
 You can update or modify records in a database.
 You can delete records from the database.
 You can set permissions or access control within the database for data security.
 You can create views to avoid typing frequently used complex queries.
The SQL keywords are words that are reserved words that are not used as a user defined data.
Types of SQL Statements
 Data Definition Language (DDL) statements are used to create, modify, or delete database objects
such as tables, views, schemas, domains, triggers, stored procedures, and the database itself. The
SQL keywords most often associated with DDL statements are CREATE, ALTER, and DROP.
 Data Control Language (DCL) protects your database from becoming corrupted. Used correctly,
the DCL provides security for your database; the amount of protection depends on the
implementation. With DCL, you can grant or restrict access by using the GRANT, Deny or
REVOKE statements. The DCL statements also allow you to control the type of access each user
has to database objects. DDL and DCL statements are commonly used by a database designer and
database administrator for establishing the database structures used by an application.
 Data Manipulation Language (DML) allows a user or an application program to update the
database by adding new data, removing old data, and modifying previously stored data. The
primary keywords associated with DML statements are INSERT, UPDATE, and DELETE.
 Data query language (DQL) When you need to retrieve data from a database, you use the SQL
language to make the request. The DBMS processes the SQL request, retrieves the requested data,
and returns it to you. You use a SELECT statement to retrieve data from a table.
The Role of SQL
• SQL is an interactive query language.
• SQL is a database programming language.
• SQL is a database administration language.
• SQL is a client/server language.
• SQL is an Internet data access language.
• SQL is a distributed database language.
• SQL is a database gateway language.
SQL has thus emerged as a useful, powerful tool for linking people, computer programs, and computer
systems to the data stored in a relational database.
Operation sheet 1.1: Install SQL server 2012
Steps to install SQL Server 2012
Step 1: Open installation media in new window and right click on setup file to run it ―As Administrator.
Step 2: Installation Center
Step 3: Setup Support Rules
Step 4: Product Key
Step 5: License Term
Step 6: Product Updates
Step 7: Install Setup Files
Step 8: Setup Support Rules
Step Step 9: Setup Role
Step 10: Components or Features to Install
Step 11: Installation Rules
Step 12: Instance Configuration
Step 13: Disk Space requirement summary
Step 14: Server Configuration
Step 15: Database Engine Configuration
Step 16: Analysis Services Configuration
Step 17: Distributed Replay Controller
Step 18: Distributed Replay Client
Step 19: Error Reporting to Microsoft.
Step 20: Installation Configuration Rule
Step 21: Installation Summary
Step 22: Ready Steady Go!
Step 23: Installation completed
Step 24: Verification

Lap Test
Task 1: Connect your SQL server
Task 2: Create a new quer
Unit Two: Data definition language
2.1. Introduction to SQL data definition language commands
The Data Definition Language (DDL) is part of SQL that you use to create (completely define) a database,
modify its structure, and destroy it when you no longer need it.
It contains SQL commands you use to create, change, or destroy the basic elements of a relational
database. Basic elements include tables, views, schemas, catalogs, clusters, indexes, stored procedures,
functions and possibly other things as well. Schema is an overall structure that includes tables within it.
Tables and schemas are two elements of a relational database‘s containment hierarchy. You can break
down the containment hierarchy as follows:
 Tables contain columns and rows.
 Schemas contain tables and views.
 Catalogs contain schemas.
2.2. Database planning
 Identify all tables.
 Define the columns that each table must contain.
 Give each table a primary key that you can guarantee is unique.
 Make sure that every table in the database has at least one column in common with one other table
in the database. These shared columns serve as logical links that enable you to relate information
in one table to the corresponding information in another table.
 Put each table in third normal form (3NF) or better to ensure the prevention of insertion, deletion,
and update anomalies.
2.1. Usage of relevant naming convention for all database elements
 Table Name
 Column Name
 Data Types
There is a standard that specifies various types of data that can be stored in SQL-based
database and manipulated by the SQL languages.
Character strings:
Data type Description Storage

char(n) or Fixed-lengthcharacterstring.Maximum8,000characters N
character(n)

varchar(n) Variable-lengthcharacterstring.Maximum8,000characters

varchar(max) Variable-lengthcharacterstring.Maximum1,073,741,824
characters

Text Variable-lengthcharacterstring.Maximum2GBoftextdata
 Unicode strings:
Data type Description Storage

nchar(n) Fixed-lengthUnicodedata.Maximum4,000characters

nvarchar(n) Variable-lengthUnicodedata.Maximum4,000characters

nvarchar(max) Variable-lengthUnicodedata.Maximum536,870,912characters

Ntext Variable-lengthUnicodedata.Maximum2GBoftextdata

 Binary types:
Data type Description Storage

Bit Allows0,1,or NULL

binary(n) Fixed-lengthbinarydata.Maximum8,000bytes

varbinary(n) Variable-lengthbinarydata.Maximum8,000bytes

varbinary(max) Variable-lengthbinarydata.Maximum2GB

Image Variable-lengthbinarydata.Maximum2GB

 Number types:
Data type Description Storage

Tiny int Allowswholenumbersfrom0to255 1byte

Small int Allowswholenumbersbetween-32,768and32,767 2bytes

Int Allowswholenumbersbetween-2,147,483,648and2,147,483,647 4bytes

Bigint Allowswholenumbersbetween-9,223,372,036,854,775,808and 8bytes

9,223,372,036,854,775,807
decimal(p,s) Fixedprecisionandscalenumbers. 5-17
bytes
Allowsnumbersfrom-10^38+1to10^38–1.
Thepparameterindicatesthemaximumtotalnumberofdigits that
canbestored(bothtotheleftandtotherightofthedecimalpoint). p must
be a value from 1 to 38. Default is 18.
Thesparameterindicatesthemaximumnumberofdigitsstoredto

therightofthedecimalpoint.smustbeavaluefrom0top. Default value

is 0

numeric(p,s) Fixedprecisionandscalenumbers. 5-17

bytes
Allowsnumbersfrom-10^38+1to10^38–1.
Thepparameterindicatesthemaximumtotalnumberofdigits that
canbestored(bothtotheleftandtotherightofthedecimalpoint). p must
be a value from 1 to 38. Default is 18.
Thesparameterindicatesthemaximumnumberofdigitsstoredto the
right of the decimal point. s must be a value from 0 to p.
Defaultvalueis0

Small money Monetarydatafrom-214,748.3648to214,748.3647 4bytes

money Monetarydatafrom-922,337,203,685,477.5808to 8bytes

922,337,203,685,477.5807

float(n) Floatingprecisionnumberdatafrom-1.79E+308to1.79E+308. 4or8

bytes
The n parameter indicates whether the field should hold 4 or 8
bytes.float(24)holdsa4-bytefieldandfloat(53)holdsan8-byte field.
Default value of n is 53.

Real Floatingprecisionnumberdatafrom-3.40E+38to3.40E+38 4 bytes

 Date types:

Data type Description Storage

datetime FromJanuary1,1753toDecember31,9999withanaccuracyof3.33 8 bytes

milliseconds

datetime2 FromJanuary1,0001andDecember31,9999withanaccuracyof100 6-8bytes

nanoseconds

Small datetime FromJanuary1,1900toJune 6,2079with anaccuracyof 1 minute 4 bytes

Date Storea dateonly.FromJanuary1,0001toDecember 31,9999 3 bytes

Time Storea timeonlytoanaccuracyof 100 nanoseconds 3-5bytes

datetimeoffset Thesameasdatetime2withtheadditionofatimezone offset 8-10bytes

timestamp Stores a unique number that gets updated every time a row gets created
or modified.Thetimestampvalueisbaseduponaninternalclockanddoesnot
correspond to real time. Each table may have only one timestamp
variable
 Other data types:

Datatype Description

sql_variant Storesupto8,000bytesofdataofvariousdatatypes,excepttext,ntext,and timestamp

uniqueidentifier Storesagloballyuniqueidentifier(GUID)

Xml StoresXMLformatted data.Maximum2GB

cursor Storesareferencetoa cursorusedfordatabase operations

Table Storesaresult-setforlaterprocessing
 Fixed-length character strings: columns holding these types of data typically store names of
people and companies, addresses, descriptions, and so on.
 Integers: columns holding this types of data typically store counts, quantities, ages, and so on.
Integer‘s columns are also frequently used to contain Id numbers, such as customers, employee.
And order numbers.
 Decimal numbers: columns with this type store numbers that have fractional parts and must
becalculated exactly, suchas rates and percentages.Theyarealso frequentlyused to store money
amounts.
 Extended data Types
 Variable-lengthCharacterstring: SQLwhichsupportsVARCHATdata.Whichallowsa
column to store character strings that vary in length from row to row, up to some maximum
length.
 Datesandtimes:supportsfordate/timevalues.
 Booleandata:supportslogical(TRUEorFALSE)valuesasanexplicittype.
 Data Types Differences
Thedifferencesb/nthedatatypesofferedinvariousSQLimplementationisoneofthepractical barriers
to the portability of SQL based applications.
Example:Date/timedataprovidesanexcellentexampleofthesesdifferences.
Date:w/cstoresadatelikeJune30,2009or30June2009
 Constants In some SQL statements a numeric, character, or date data value must be expressed in text
form. For example: INSERT statement, w/c adds a student to the database:

INSERT INTO student(Fname, SID, Dept, year)

The value for each column in the newly inserted row is specified in the VALUES clause.
Constant data values are also used in expression such as in the SELECT statement

SELECT
city
FROMoffice

 Numeric constant
Integers and decimal constants (also called exact numeric literals) are written as ordinary decimal
numbers in SQL statements, with an optional leading plus or minus sign
Example:200 +345.95 -500 789.00
Use a comma between the digits of a numeric constant
 String Constant
TheANSI/ISOstandardspecifiesthatSQLconstantsforcharacterdatabeenclosedinsingle quotes
(‘……’)
Example:‗WaksumMotuma‘ ‗Addis Ababa‘
Ifasinglequotesistobeusedincludedintheconstanttext,itis writtenwithintheconstantastwo
consecutive single quote characters. This isconstant value:
Example:―Ican‘t‖
 Date and time constants
InSQLproductsthesupportsdate/timedata.Constantvaluesfordates,times,andtimeintervalsare
specified as string constants.The format of these constants varies from one DBMS to the next.
Example:

SELECTName,dept

FROMstudent

WHEREhire-date=To-date(‗June30,2009‘,‗monDDyyyy‘)
 Expressions
ExpressionsareusedintheSQLLanguagestocalculatevaluesthatareretrievedfromthedatabaseand
to calculate values used in searching the database.
Example1:Thequerythatcalculatesthe sales ofeachoffices asapercentageofitstarget:

SELECTcity,target,sales,(sales/target)*100

FROMoffices
Example2:

SELECT city
FROMoffices
WHEREsales>target+50000.00

 Missing data (Null Values)

It is a value whose value is missing, unknown, or don‘t apply. SQL supports missing, unknown, or
inapplicable data explicitly through the concepts of null values. A null Value is an indicator that
tells SQL (and the user) that the data is missing or not applicable.
Example: given a student table below that has missing values

2.2. Database structure creation and manipulation

Remember that a database is designed, built and populated with data for a specific purpose that is designed
to address a specific problem in the real world.
Once you have completed database design, you can implement it using a specific DBMS. You may, for
example, create a CUSTOMER table with the attributes CUSTOMER. CustomerID,
CUSTOMER.FirstName, CUSTOMER. LastName, CUSTOMER.Street, CUSTOMER.City,
CUSTOMER.State, CUSTOMER.Zipcode, and CUSTOMER.Phone. All of these attributes are more
closely related to the customer entity than to any other entity in a database that may contain many tables.
These attributes contain all the relatively permanent customer information that your organization keeps on
file.Most database management systems provide a graphical tool for creating database tables. You use SQL
Server management studio which is a graphical tool provided by Microsoft to connect to MS SQL Server
for creating database tables You can also create such tables by using an SQL command. Since a database
contains all other objects including tables, you need to create database first before you create tables and
other objects.
The SQL CREATE DATABASE statement is used to create new SQL database.
Syntax:
 Basic syntax of CREATE DATABASE statement is as follows:
CREATE DATABASE DatabaseName;
Always database name should be unique within the RDBMS.
Example:
If you want to create new database <testDB>, then CREATE DATABASE statement would be as
follows: Eg. Create database testDB
You need to execute the command to create the specified database. Once you create a database and made it
the active (currently selected) database, now you can create as many objects as you want including tables.
The following example demonstrates a command that creates your CUSTOMER table:
CREATE TABLE CUSTOMER (customerid INTEGER NOT NULL, firstname CHARACTER (15),
lastname CHARACTER (20) NOT NULL, Street CHARACTER (25), City CHARACTER (20), State
CHARACTER (2), Zipcode INTEGER, Phone CHARACTER (13) ) ;
For each column, you specify its name (for example, CustomerID), its data type (for example, INTEGER),
and possibly one or more constraints (for example, NOT NULL).
View:- At times, you want to retrieve specific information from the CUSTOMER table.
You don‘t want to look at everything — only specific columns and rows. What you need is a view.
A view is a virtual table. In most implementations, a view has no independent physical existence. The
view‘s definition exists only in the database‘s metadata, but the data comes from the table or tables from
which you derive the view. The view‘s data is not physically duplicated somewhere else in online disk
storage. Some views consist of specific columns and rows of a single table. Others, known as multi table
views, draw from two or more tables. Eg. Of SQL DDL statement to create a view
CREATE VIEW NH_CUST AS SELECT CUSTOMER.FirstName, CUSTOMER.LastName,
CUSTOMER.Phone FROM CUSTOMER WHERE CUSTOMER.State = ‗NH‘

 Relationships
If one table in a database contains as a foreign key a column that is a primary key in another table in the
database, you can add a constraint to the first table so that it references the second table.
Example:
CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT
NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
CREATE TABLE ORDERS (ID INT NOT NULL, O_DATE DATETIME, CUSTOMER_ID INT foreign
key references CUSTOMERS(ID),AMOUNT Decimal(8,2), PRIMARY KEY (ID));
For defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT
NOT NULL, ADDRESS CHAR (25),SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME));
CHECK Constraint:
The CHECK Constraint enables a condition to check the value being entered into a record. If the
condition evaluates to false, the record violates the constraint and isn‘t entered into the table.
Example:
For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here,
we add a CHECK with AGE column, so that you cannot have any CUSTOMER below 18 years:
CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL CHECK (AGE >= 18), ADDRESS CHAR (25) ,SALARY DECIMAL (18, 2),
PRIMARY KEY (ID));
SQL NOT NULL Constraint
 By default, a table column can hold NULL values.
 The NOT NULL constraint enforces a column to NOT accept NULL values.
 The NOT NULL constraint enforces a field to always contain a value. This means that
you cannot insert a new record, or update a record without adding a value to this field.
 The following SQL enforces the "P_Id" column and the "LastName" column to not

CREATETABLEPersons
(P_IdintNOTNULL,
LastNamevarchar(255)NOTNULL, FirstName
varchar(255),
Addressvarchar(255),
City varchar(255))

accept NULL values:

SQL UNIQUE Constraint
 The UNIQUE constraint uniquely identifies each record in a database table.
 The UNIQUE and PRIMARY KEY constraints both provide a guarantee for
uniqueness for a column or set of columns.
 A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.
 Note that you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.
SQL UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

CREATETABLEPersons (P_IdintNOTNULLUNIQUE,

LastNamevarchar(255)NOTNULL, FirstName varchar(255),

Addressvarchar(255),
City varchar(255))
To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns,
use the following SQL syntax:

CREATETABLEPersons (P_IdintNOTNULL,

LastNamevarchar(255)NOTNULL, FirstName varchar(255),

Addressvarchar(255), City varchar(255),

CONSTRAINTuc_PersonIDUNIQUE(P_Id,LastName))
SQL DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.
The following SQL creates a DEFAULT constraint on the "City" column when the ―Persons"
table is created:
Create table persons(P_IdintNOTNULL,LastNamevarchar(255)
NOTNULL, FirstName
varchar(255),Addressvarchar(255),
Cityvarchar(255)DEFAULT'Sandnes')

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
Create table orders (oid int not null, ono int not null)
 SQL AUTO INCREMENT Field
Auto-increment allows a unique number to be generated when a new record is inserted in to a table.
 AUTO INCREMENT a Field

Very often we would like the value of the primary key field to be created automatically every
time a new record is inserted.
We would like to create an auto-increment field in a table.
SyntaxforSQLServer

ThefollowingSQLstatementdefinesthe"P_Id"columntobeanauto-incrementprimarykey field in
the "Persons" table:

CREATETABLEPersons (

P_IdintPRIMARYKEYIDENTITY,
LastNamevarchar(255)NOTNULL, FirstName
varchar(255),

Addressvarchar(255),
City varchar(255))

TheMSSQLServerusestheIDENTITYkeywordtoperformanauto-incrementfeature.
Bydefault,thestartingvaluefor IDENTITYis1,anditwillincrementby1foreachnew record.
Tospecifythatthe"P_Id"columnshouldstartat value10andincrementby5,changethe identity to
IDENTITY(10,5).
Toinsertanewrecordintothe"Persons"table,wewillnothavetospecifyavalueforthe "P_Id" column (a
unique value will be added automatically):
INSERTINTOPersons(FirstName,LastName) VALUES
('Lars','Monsen')

The SQL statement above would insert a new record into the "Persons" table. The "P_Id"
columnwouldbeassignedauniquevalue.The"FirstName"columnwouldbesetto"Lars"and the
"LastName" column would be set to "Monsen".
 ALTER
After you create a table, you‘re not necessarily stuck with that exact table forever. As you use the table,
you may discover that it‘s not everything you need it to be. You can use the ALTER TABLE command to
change the table by adding, changing, or deleting a column in the table. In addition to tables, you can also
ALTER columns and domains.To create a PRIMARY KEY constraint on the "ID" column when
CUSTOMERS table above already exists, use the following SQL syntax:
ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
NOTE: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must
already have been declared to not contain NULL values (when the table was first created).
If ORDERS table has already been created, and the foreign key has not yet been set, use the syntax for
specifying a foreign key by altering a table. ALTER TABLE ORDERS ADD FOREIGN KEY
(Customer_ID) REFERENCES CUSTOMERS (ID);
To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table
already exists, use the following SQL syntax:
ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);
If ORDERS table has already been created, and the foreign key has not yet been set, use the syntax for
specifying a foreign key by altering a table.
ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you
would write a statement similar to the following:
ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraintCHECK(AGE >= 18);
SQL UNIQUE Constraint on ALTER TABLE
To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following
SQL:
ALTERTABLEPersons ADD UNIQUE (P_Id)

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple
columns, use the following SQL syntax:

ALTERTABLEPersons
ADDCONSTRAINTuc_PersonIDUNIQUE(P_Id,LastName)

 SQL DEFAULT Constraint on CREATE TABLE

TocreateaDEFAULTconstraintonthe"City"columnwhenthetableisalreadycreated,use the following
SQL:

ALTERTABLEPersons
ALTERCOLUMNCitySETDEFAULT'SANDNES'

 DROP
Removing a table from a database schema is easy. Just use a DROP TABLE <tablename>command. You
erase all the table‘s data as well as the metadata that defines the table in the data dictionary. It‘s almost as
if the table never existed. Eg Drop table Customers.
Delete Primary Key:-You can clear the primary key constraints from the table, Use Syntax:
ALTER TABLE CUSTOMERS DROP PRIMARY KEY PK_CUSTID;
To drop a FOREIGN KEY constraint, use the following SQL:
ALTER TABLE ORDERS DROP FOREIGN KEYCustomer_ID;
TodropaUNIQUEconstraint,usethefollowing SQL:

ALTERTABLEPersons DROPCONSTRAINTuc_PersonID

To drop a DEFAULT constraint, use the following SQL:

ALTERTABLEPersons
ALTERCOLUMNCityDROPDEFAULT

DROP or DELETE Database

The SQL DROP DATABASE statement is used to drop an existing database in SQL schema.
Basic syntax of DROP DATABASE statement is as follows:
DROP DATABASE DatabaseName;
Example:
If you want to delete an existing database <testDB>, then DROP DATABASE statement would be as
follows: Drop database testDB
The sporting goods store database contains four tables: CUSTOMER, PRODUCT, INVOICE, and
INVOICE_LINE.
Exercise Write the SQL DDL command that implements the database. Name the database
Sporting_goods.
Table Column Data type Constraint
CUSTOMER CustomerID INTEGER Primary key

FirstName CHARACTER (15)

LastName CHARACTER(20) NOT NULL

Street CHARACTER (25)

City CHARACTER (20)

State CHARACTER (2)

Zipcode INTEGER

Phone CHARACTER (13)

PRODUCT ProductID INTEGER Primary key

Name CHARACTER (25)

Description CHARACTER (30)

Category CHARACTER (15)

VendorID INTEGER

VendorName CHARACTER (30)

INVOICE InvoiceNumber INTEGER Primary key

CustomerID INTEGER

InvoiceDate DATE

INVOICE_LINE LineNumber INTEGER Primary key

InvoiceNumber Integer

ProductID INTEGER

Quantity INTEGER

SalePrice NUMERIC (9,2)

Notice that some of the columns in the above Table contain the constraint primary key and NOT NULL.
These columns are either the primary keys of their respective tables or columns that you decide must
contain a value. A table‘s primary key must uniquely identify each row. To do that, the primary key must
contain a nonnull value in every row. The tables relate to each other through the columns that they have in
common.
The following list describes these relationships.
The CUSTOMER table bears a one-to-many relationship to the INVOICEtable. One customer can make
multiple purchases, generating multiple invoices. Each invoice, however, deals with one and only one
customer.
The INVOICE table bears a one-to-many relationship to the INVOICE_LINEtable. An invoice may have
multiple lines, but each line appears on one and only one invoice.
The PRODUCT table also bears a one-to-many relationship to the INVOICE_LINE table. A product may
appear on more than one line on one or more invoices. Each line, however, deals with one, and only
oneproduct.
The CUSTOMER table links to the INVOICE table by the common CustomerID column. The INVOICE
table links to the INVOICE_LINE table by the common InvoiceNumber column. The PRODUCT table
links to the INVOICE_LINE table by the common ProductID column. These links are what makes this
database a relational database.
Lap Test
Instruction: Given necessary tools and materials you are required to perform the
following tasks accordingly
Task 1: Create a database called Hospital
Task 2: Create a table called Doctor, Patient and medicine with their respected relations
Task 3: Display a relationship you have created
Unit Three: Data manipulation language
3.1. Overview of SQL data manipulation language commands
The DDL is the part of SQL that creates, modifies, or destroys database structures; it doesn‘t deal with the
data. The Data Manipulation Language (DML)is the part of SQL that operates on the data. Some DML
statements read likeordinary English-language sentences and are easy to understand. Because SQLgives
you very fine control of data, other DML statements can be fiendishly
complex. If a DML statement includes multiple expressions, clauses, predicates, or subqueries,
understanding what that statement is trying to do canbe a challenge.
3.2. Data insertion
SQL INSERT Query
The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.
Syntax:
There are two basic syntaxes of INSERT INTO statement as follows:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN) VALUES (value1, value2,
value3,...valueN);
Here, column1, column2,...columnN are the names of the columns in the table into which you want to
insert data.
You may not need to specify the column(s) name in the SQL query if you are adding values for all the
columns of the table. But make sure the order of the values is in the same order as the columns in the table.
The SQL INSERT INTO syntax would be as follows:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
Example:
Following statements would create six records in CUSTOMERS table:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );
You can create a record in CUSTOMERS table using second syntax as follows
INSERT INTO CUSTOMERS VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
All the above statements would produce the following records in CUSTOMERS table:
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +

ThefollowingSQLstatementwilladdanewrow, butonlyadddatainthe "P_Id","LastName" and the

INSERTINTOPersons(P_Id,LastName,FirstName) VALUES
(5, 'Tjessem', 'Jakob')

"FirstName" columns:

The"Persons"tablewillnowlooklikethis:
P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

4 Nilsen Johan Bakken2 Stavanger

5 Tjessem Jakob

3.3. Modification of existing data

SQL UPDATE Query
The SQL UPDATE Query is used to modify the existing records in a table.
You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be
affected.
Syntax:
The basic syntax of UPDATE query with WHERE clause is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
You can combine N number of conditions using AND or OR operators.
Example:
Consider the CUSTOMERS table having the following records:
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
Following is an example, which would update ADDRESS for a customer whose ID is 6:
UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;
Now, CUSTOMERS table would have the following records:
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | Pune | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
If you want to modify all ADDRESS and SALARY column values in CUSTOMERS table, you do not
need to use WHERE clause and UPDATE query would be as follows:
UPDATE CUSTOMERS SET ADDRESS = 'Pune', SALARY = 1000.00;
Now, CUSTOMERS table would have the following records:
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Pune | 1000.00 |
| 2 | Khilan | 25 | Pune | 1000.00 |
| 3 | kaushik | 23 | Pune | 1000.00 |
| 4 | Chaitali | 25 | Pune | 1000.00 |
| 5 | Hardik | 27 | Pune | 1000.00 |
| 6 | Komal | 22 | Pune | 1000.00 |
| 7 | Muffy | 24 | Pune | 1000.00 |
+ + + + + +

3.4. Data deletion

SQL DELETE Query
The SQL DELETE Query is used to delete the existing records from a table.
You can use WHERE clause with DELETE query to delete selected rows, otherwise all the
records would be deleted.
The basic syntax of DELETE query with WHERE clause is as follows:
DELETE FROM table_name WHERE [condition];
You can combine N number of conditions using AND or OR operators.
Example:Consider the CUSTOMERS table having the following records:
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
Following is an example, which would DELETE a customer, whose ID is 6:
DELETE FROM CUSTOMERS WHERE ID = 6;
Now, CUSTOMERS table would have the following records:
+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+ + + + + +
If you want to DELETE all the records from CUSTOMERS table, you do not need to use
WHERE clause and DELETE query would be as follows:
DELETE FROM CUSTOMERS;
Now, CUSTOMERS table would not have any record

Lap Test
Instruction: Given necessary tools and materials you are required to perform the
following tasks accordingly
Task 1:Create a database for called AB_Supermarket
Task 2: Create and insert the following data to the Customers and customer_ordertable
Customer Table
Unit Four: Data query language

1.1. Overview of SQL data query language

In many ways, queries are the heart of the SQL language. The SELECT statement, which is used to
express SQL queries, is the most powerful and complex of the SQL statements.
Despite the many options afforded by the SELECT statement, it's possible to start simply and then work up
to more complex queries.
The SELECT statement retrieves data from a database and returns it to you in the form of query results.
For simple queries, the English language request and the SQL SELECT statement are very similar. When
the requests become more complex, more features of the SELECT statement must be used to specify the
query precisely. The full form of the SELECT statement consists of six clauses. The SELECT and FROM
clauses of the statement are required. The remaining four clauses are optional. You include them in a
SELECT statement only when you want to use the functions they provide.
The SELECTclause lists the data items to be retrieved by the SELECTstatement. The items may be
columns from the database, or columns to be calculated by SQL as it performs the query.
The SELECT clause that begins each SELECT statement specifies the data items to be retrieved by the
query. The items are usually specified by a select list, a list of select items separated by commas. Each
select item in the list generates a single column of query results, in left-to-right order. A select item can be:
• a column name, identifying a column from the table(s) named in the FROM clause.
When a column name appears as a select item, SQL simply takes the value of that column from each row
of the database table and places it in the corresponding row of query results.
• a constant, specifying that the same constant value is to appear in every row of the query results.
• a SQL expression, indicating that SQL must calculate the value to be placed into the query results, in the
style specified by the expression. The result is stored in a result table, called the result-set.
Each type of select item is described later in this unit.
The FROM clause lists the tables that contain the data to be retrieved by the query. Queries tcan draw their
data from a single or more tables.
The FROM clause consists of the keyword FROM, followed by a list of table specifications separated by
commas. Each table specification identifies a table containing data to be retrieved by the query. These
tables are called the source tablesof the query (and of the SELECTstatement) because they are the source
of all of the data in the query results.
The WHERE clause tells SQL to include only certain rows of data in the query results. A search condition
is used to specify the desired rows.
The GROUP BY clause specifies a summary query. Instead of producing one row of query results for each
row of data in the database, a summary query groups together similar rows and then produces one
summary row of query results for each group.
The HAVING clause tells SQL to include only certain groups produced by the GROUP BY clause in the
query results. Like the WHERE clause, it uses a search condition to specify the desired groups.
The ORDER BY clause sorts the query results based on the data in one or more columns.
If it is omitted, the query results are not sorted. Query
Results
The result of a SQL query is always a table of data, just like the tables in the database. If you type a
SELECT statement using interactive SQL, the DBMS displays the query results in tabular form on your
computer screen. If a program sends a query to the DBMS using programmatic SQL, the table of query
results is returned to the program. In either case, the query results always have the same tabular,
row/column format as the actual tables in the database
1.2. Selection of data from a single table
SQL SELECT Syntax

SELECTcolumn_name(s)
FROM table_name

and

SELECT*FROMtable_name

Note:SQLisnotcasesensitive.SELECTisthesameasselect.
An SQL SELECT Example, The "Persons" table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

Nowwewanttoselectthecontentofthecolumnsnamed"LastName"and"FirstName"from the table above.

WeusethefollowingSELECTstatement:

SELECTLastName, FirstNameFROMPersons

Theresult-setwilllooklikethis:

LastName FirstName

Hansen Ola

Svendson Tove

Pettersen Kari

SELECT * Example

Nowwewanttoselectallthecolumnsfromthe"Persons"table. We use the following SELECT statement:

SELECT*FROMPersons

Tip:Theasterisk(*)isaquickwayofselectingallcolumns! The result-set will look like this:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

 NavigationinaResult-set
Mostdatabasesoftwaresystemsallownavigationintheresult-setwithprogrammingfunctions, like:
Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.
Programmingfunctionslikethesearenotapartofthistutorial.
1.3. Retrieval of data selectively
 SQLSELECTDISTINCTStatement
Inatable,someofthecolumnsmaycontainduplicatevalues.Thisisnotaproblem,however, sometimes you will
want to list only the different (distinct) values in a table.
TheDISTINCTkeywordcanbeusedtoreturnonlydistinct(different)values.
SQL SELECT DISTINCT Syntax

SELECTDISTINCTcolumn_name(s) FROMtable_name

SELECTDISTINCTExample: The"Persons"table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavange

r
Nowwewant toselect onlythedistinctvaluesfromthecolumnnamed"City"fromthetable above.
WeusethefollowingSELECTstatement:
Theresult-setwilllooklikethis:
SELECTDISTINCTCityFROMPersons
City

Sandnes
Stavanger

 SQL WHERE Clause

 TheWHEREclauseisusedtofilterrecords.
TheWHEREclauseisusedtoextractonlythoserecordsthatfulfillaspecifiedcriterion.
SQLWHERESyntax

SELECTcolumn_name(s)
FROM table_name

WHEREcolumn_nameoperatorvalue

WHEREClauseExample
The"Persons"table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

Nowwewanttoselectonlythepersonslivinginthecity"Sandnes"fromthetableabove. We use the

following SELECT statement:

SELECT*FROMPersons WHERECity='Sandnes'

 QuotesaroundTextFields
SQLusessinglequotesaroundtextvalues(mostdatabasesystemswillalsoacceptdouble quotes).
Although,numericvaluesshouldnotbeenclosedinquotes. For
Thisiscorrect:
SELECT*FROMPersonsWHEREFirstName='Tove'
is wrong:
SELECT*FROMPersonsWHEREFirstName=Tove
text values:
Fornumericvalues:

Thisiscorrect:
SELECT*FROMPersonsWHEREYear=1965 This
is wrong:

SELECT*FROMPersonsWHEREYear='1965'

OperatorsAllowedintheWHERE Clause
WiththeWHEREclause,thefollowingoperatorscanbeused:

Operator Description
= Equal
<> Not equal
> Greaterthan
< Lessthan
>= Greaterthanorequal
<= Lessthan orequal
BETWEE Betweenaninclusiverange
N
LIKE Searchfora pattern
IN Ifyouknowtheexactvalueyouwanttoreturnforatleastoneofthe columns

Note:InsomeversionsofSQLthe<>operatormaybewrittenas!=

 SQLAND&OR Operators
TheAND&ORoperatorsareusedtofilterrecordsbasedonmorethanonecondition.
TheANDoperatordisplaysarecordifboththefirstandthesecondconditionistrue.
TheORoperatordisplaysarecordifeitherthefirstconditionorthesecondconditionistrue.
ANDOperatorExample
The"Persons"table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavange

r
Nowwewant toselect onlythepersonswiththefirstnameequalto "Tove"ANDthelastname equal to
"Svendson":
WeusethefollowingSELECTstatement:

SELECT * FROM Persons

WHERE FirstName='Tove'
ANDLastName='Svendson'

Theresult-setwilllooklikethis:

P_Id LastName FirstName Address City

2 Svendson Tove Borgvn23 Sandnes
OR Operator Example
Now we want to select only the persons with the first name equal to "Tove"ORthefirstname
equal to "Ola":
We use the following SELECT statement:
SELECT*FROMPersons
WHEREFirstName='Tove'
OR FirstName='Ola'

Theresult-setwilllooklikethis:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

CombiningAND&OR
YoucanalsocombineANDandOR(useparenthesistoformcomplexexpressions).
Nowwewant toselect onlythepersonswiththelastnameequalto "Svendson"ANDthefirst name
equal to "Tove" OR to "Ola":
WeusethefollowingSELECTstatement:

SELECT*FROMPersonsWHERE LastName='Svendson'

AND(FirstName='Tove'ORFirstName='Ola')

Theresult-setwilllooklikethis:

P_Id LastName FirstName Address City

2 Svendson Tove Borgvn23 Sandnes

 SQLORDERBYKeyword
TheORDERBYkeywordisusedtosorttheresult-set.
TheORDERBYkeywordisusedtosorttheresult-setbyaspecifiedcolumn. The
ORDER BY keyword sort the records in ascending order by default.
If youwanttosorttherecordsinadescendingorder,youcanusetheDESCkeyword.
SQLORDERBYSyntax
SELECTcolumn_name(s)
FROM table_name

ORDERBYcolumn_name(s)ASC|DESC

ORDERBYExample
The"Persons"table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

4 Nilsen Tom Vingvn 23 Stavanger

SELECT*FROMPersons
ORDER BY LastName
Nowwewanttoselectallthepersonsfromthetableabove,however,wewanttosortthe persons by their last name.
WeusethefollowingSELECTstatement:
Theresult-setwilllooklikethis:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

4 Nilsen Tom Vingvn 23 Stavanger

3 Pettersen Kari Storgt20 Stavanger

2 Svendson Tove Borgvn 23 Sandnes

ORDERBYDESCExample
Nowwewanttoselectallthepersonsfromthetableabove,however,wewanttosortthe persons descending
by their last name.
WeusethefollowingSELECTstatement:

SELECT * FROM Persons

ORDERBYLastNameDESC

Theresult-setwilllooklikethis:

P_Id LastName FirstName Address City

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavange

r
4 Nilsen Tom Vingvn 23 Stavange
r
1 Hansen Ola Timoteivn10 Sandnes

 SQLUPDATEStatement
TheUPDATEstatementisusedtoupdateexistingrecordsinatable.

UPDATEtable_name
SETcolumn1=value,column2=value2,... WHERE
some_column=some_value

SQLUPDATESyntax

Note:NoticetheWHEREclauseintheUPDATEsyntax.TheWHEREclausespecifieswhich record or
records that should be updated. If you omit the WHERE clause, all records will be updated!
SQLUPDATEExample
The"Persons"table:

P_I LastName FirstName Address City

d
1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

4 Nilsen Johan Bakken2 Stavanger

5 Tjessem Jakob

Nowwewanttoupdatetheperson"Tjessem,Jakob"inthe"Persons"table. We use
the following SQL statement:

UPDATEPersons
SETAddress='Nissestien67',City='Sandnes'

WHERELastName='Tjessem'ANDFirstName='Jakob'

The"Persons"tablewillnowlooklikethis:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

4 Nilsen Johan Bakken2 Stavanger

5 Tjessem Jakob Nissestien67 Sandnes

 SQLUPDATEWarning
Becarefulwhenupdatingrecords. IfwehadomittedtheWHEREclauseintheexampleabove, like this:
UPDATEPersons
SETAddress='Nissestien67',City='Sandnes'

The"Persons"tablewouldhavelookedlikethis:

P_Id LastName FirstName Address City

1 Hansen Ola Nissestien67 Sandnes

2 Svendson Tove Nissestien67 Sandnes

3 Pettersen Kari Nissestien67 Sandnes

4 Nilsen Johan Nissestien67 Sandnes

5 Tjessem Jakob Nissestien67 Sandnes

 SQLDELETEStatement
TheDELETEstatementisusedtodeleterows/recordsinatable.
SQLDELETESyntax

DELETE FROM table_name

WHEREsome_column=some_value

Note:NoticetheWHEREclauseintheDELETEsyntax.TheWHEREclausespecifieswhich record or
records that should be deleted. If you omit the WHERE clause, all records will be deleted!
SQLDELETEExample
The"Persons"table:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn10 Sandnes

2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavange

r
4 Nilsen Johan Bakken2 Stavange
r
5 Tjessem Jakob Nissestien67 Sandnes

DELETEFROMPersons
WHERELastName='Tjessem'ANDFirstName='Jakob'

Nowwewanttodeletetheperson"Tjessem,Jakob"inthe"Persons"table. We use
the following SQL statement:
The"Persons"tablewillnowlooklikethis:

P_Id LastName FirstName Address City

1 Hansen Ola Timoteivn1 Sandnes

0
2 Svendson Tove Borgvn23 Sandnes

3 Pettersen Kari Storgt20 Stavanger

4 Nilsen Johan Bakken2 Stavanger

 DeleteAllRows

Itis possibletodeleteallrowsinatablewithoutdeletingthetable.This meansthatthetable structure,

attributes, and indexes will be intact:

DELETEFROMtable_nameor

DELETE*FROMtable_name

Note:Beverycarefulwhendeletingrecords.Youcannotundothisstatement!
1. CharacterandStringComparisonOperators:

 =(Equalto):Comparesiftwocharacterorstringvaluesareidentical.

!=or<>(Notequalto):Checksiftwocharacterorstringvaluesarenotidentical.

LIKE:Comparesacharacterorstringvaluewith apattern,oftenusingwildcardcharacters
(% and _).
Example:

 DateandTimeComparisonOperators:
 =(Equalto):Comparesiftwodateortimevaluesareequal.
 !=or<>(Notequalto):Comparesiftwodateortimevaluesarenotequal.
 <(Lessthan):Checksifonedateortimevalueisearlierthananother.
 >(Greaterthan):Checksifonedateortimevalueislaterthananother.
 <=(Lessthanorequalto):Checksifonedateortimevalueisearlierthanorequalto another.
 >=(Greaterthanorequalto):Checksifonedateortimevalueislaterthanorequalto another.
Example:

When using these comparison operators in your SQL queries, be sure to match the data types
appropriately. For example, when comparing dates, make sure the date format matches the one
used in your database. Also, consider the collation (sorting and comparison rules) for character
and string data when using comparison operators for text-based data.
Tocontroltheorderofevaluationandensurecorrectprecedence, youcan useparenthesesto group
conditions.
Example:
SELECT*FROMemployeesWHERE(age>=30ORexperience_years>=5)AND department =
'Engineering';
Inthisquery,theconditionsinsidetheparenthesesareevaluatedfirst,andthentheAND condition is applied to
the results.
Using parentheses is especially important when combining AND and OR operators in the same
query to make your intentions explicit and avoid unexpected behavior.
Understandingthe correctprecedenceandusingparentheseswhennecessaryis crucialtoensure that
your SQL queries produce the desired results and correctly evaluate complex conditions.
2. Checkingforarangeofvalues

Youcanusecomparisonoperatorstofilterrowsbasedonarangeofvalues.Forexample,to retrieve
products with prices between $50 and $100:
SELECT*FROMproductsWHEREprice>=50ANDprice<=100;
Thisqueryretrievesrows fromthe"products"tablewherethe"price"is greaterthanorequalto 50
and less than or equal to 100.
 Selectingvaluesfromalist
You can use the IN operator to filter rows based on a list of values. For example, to retrieve
orders from customers in New York or California:
SELECT*FROMordersWHEREcustomer_stateIN('NewYork','California');
This query retrieves rows from the "orders" table where the "customer_state" is either 'New
York' or 'California'.
 CheckingforValuesthatMatchaPattern
You can use the LIKE operator with wildcard characters to match values that fit a specific
pattern. For example, to retrieve products with names starting with 'Laptop':
SELECT*FROMproductsWHEREproduct_nameLIKE'Laptop%';

This query retrieves rows from the "products" table where the "product_name" starts with
'Laptop'. The '%' wildcard matches any sequence of characters, so it matches 'Laptop,' 'Laptop
Pro,' 'Laptop 2023,' and so on.
You can also use the NOT LIKE operator to exclude rows that match a particular pattern. For
example, to retrieve products with names that don't start with 'Accessory':
SELECT*FROMproductsWHEREproduct_nameNOTLIKE'Accessory%';
Thisqueryretrievesrowswherethe"product_name"doesnotstartwith'Accessory'.
These SQL techniques are commonly used to filter and retrieve specific data from a database
based on various criteria, making it easier to work with the data that meets your specific
requirements.
 Takingactiontoexecutenullvaluesfromaqueryresult
In SQL, you can take action to handle and filter out null values from a queryresult using the IS
NULL and IS NOT NULL operators. Here's how you can use them:
3. FilteringRowswithNullValues(ISNULL):

Toretrieverowsthatcontainnullvaluesinaspecificcolumn,youcanusetheISNULL
operator.Forexample,toretrieveallproductswithnullvaluesinthe"description"column:
Thisquerywillreturnrowswherethe"description"columnisnull.
 FilteringRowswithoutNullValues(ISNOTNULL):

To retrieve rows that do not contain null values in a specific column, you can use the IS NOT
NULL operator. For example, to retrieve all products with a non-null value in the "price"
column:
SELECT*FROMproductsWHEREpriceISNOTNULL;
Thisquerywillreturnrowswherethe"price"columnisnotnull.
 HandlingNullValuesintheResult(COALESCE):
If you want to replace null values in the query result with a specific default value, you can use
the COALESCE function. For example, if you want to replace null values in the "description"
column with "No description available":
SELECTproduct_name,COALESCE(description,'Nodescriptionavailable')ASdescription
FROM products;
This query uses the COALESCE function to replace null values with the specified default
value in the result set.
Handling null values is important to ensure the accuracy and completeness of your query
results. Themethodsmentioned aboveallow youto filter andmanagenull values effectivelyin
yourSQL queries.
1.4. Working with functions
 Using arithmetical operators with the correctprecedence
 Arithmetic Operators
You can use arithmetic operators in Multidimensional Expressions (MDX) for any
arithmeticcomputations, including addition, subtraction, multiplication, and division.
MDXsupportsthearithmeticoperatorslistedinthefollowingtable.
Operator Description
+ (Add) Addstwonumbers.

/ (Divide) Dividesonenumberbyanothernumber.

*(Multiply) Multipliestwonumbers.

- (Subtract) Subtractstwonumbers.

^(Power) Raisesonenumberbyanothernumber.

Note:MDXdoes notincludea functiontoobtain thesquare rootofanumber.Toobtainthesquare root of a

number, raise it to the power of 0.5 using the ^ operatior.
 Orderof Precedence

ThefollowingrulesdeterminetheorderofprecedenceforarithmeticoperatorsinanMDX expression:
 Whenthereismorethanonearithmeticoperatorinanexpression,MDXperforms
multiplication and division first, followed by subtraction and addition.
 Whenallarithmeticoperatorsinanexpressionhavethesamelevelofprecedence,the order
of execution is left to right.

 Expressionswithinparenthesestakeprecedenceoverallotheroperations.

In SQL, you can perform arithmetic operations using operators such as +, -, *, and /. It's
important to understand operator precedence when combining multiple operators in an
expression.
Example: Suppose you have a table called "Products" with columns "Price" and
"Quantity."You want to calculate the total cost for each product, considering the unit price and
quantity. The SQL statement would be:
SELECTProductName,(Price*Quantity)ASTotalCost FROM Products;

In this example, the * operator is used to multiply the "Price" and "Quantity" columns to obtain
the "TotalCost."
 UsingStringFunctionsandOperators:
SQL provides various string functions and operators for working with text data. For
instance,you can use the CONCAT function to concatenate strings.
Example:
Suppose you have a tablecalled "Employees"with columns "FirstName" and "LastName."You
want to create a single string representing the full name. The SQL statement would be:

Inthisexample,theCONCATfunctionisusedtocombinethe"FirstName" and"LastName" columns

with a space in between.
 UsingMathematicalFunctions:
SQLoffersmathematicalfunctionsforperformingoperationslikerounding,absolutevalue, square
root, etc.

Example:
Suppose you have a table called "Orders" with a "TotalAmount" column. You want to calculate
the square root of the total amount. The SQL statement would be:
SELECTOrderID,SQRT(TotalAmount)ASSquareRootAmount FROM Orders;
Inthisexample,theSQRTfunctioncalculatesthesquarerootofthe"TotalAmount."
 UsingDateFunctions:
SQLprovidesvariousdatefunctionsforworkingwithdateandtimedata. Example:
Suppose you have a table called "Events" with a "EventDate" column, and you want to find the
events that occurred within the last 30 days. The SQL statement would be:
SELECTEventName,EventDate FROM Events WHEREEventDate>=DATEADD(day,-
30,GETDATE());
Inthisexample,theDATEADDfunctionsubtracts30daysfromthecurrentdate (GETDATE()), and the
WHERE clause filters events that occurred after that date.
 UsingSQLAggregateFunctions:
SQLaggregatefunctionsareusedtoperformcalculationsonsetsofvalues.
AVG()-TheAVG()functionreturnstheaveragevalueofanumericcolumn.
AVG()Syntax
SELECTAVG(column_name) FROM table_nameWHEREcondition;
Example
COUNT()–TheCOUNT()functionreturnsthenumberofrowsthatmatchesaspecified criterion.
COUNT()Syntax
SELECTCOUNT(column_name) FROM table_nameWHEREcondition;
Example

MAX() - Returns the largest value

MIN()-Returnsthesmallestvalue
SUM()-TheSUM()functionreturnsthetotalsumofanumericcolumn.
SUM()Syntax
SELECTSUM(column_name) FROM table_name
WHEREcondition;
Example:
Suppose youhaveatablecalled"Orders"withan"OrderAmount"column,andyouwant tofind the
total sales for a specific period. The SQL statement would be:
In this example, the SUM function calculates the total sales for orders placed between
January1, 2023, and December 31, 2023.
These examples demonstrate the use of arithmetical, string, mathematical, date, and
aggregate functions in SQL queries to obtain the desired query output, depending on the
data and calculations you need.
 SQLstatementsthatuseaggregationandfiltering
UsingClausetoAggregateDatabyMultipleColumns:
When you want to aggregate data based on multiple columns, you can use the GROUP BY
clause. This clause allows you to group rows by one or more columns and apply aggregate fun
SQL
 GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate functions to group the
result-set by one or more columns.
SQLGROUPBYSyntax
 SELECTcolumn_name,aggregate_function(column_name)
 FROMtable_name

 WHEREcolumn_nameoperatorvalue

 GROUPBYcolumn_name

SQLGROUPBYExample1

Wehavethefollowing"Orders"table:

O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen

2 2008/10/23 1600 Nilsen

3 2008/09/02 700 Hansen

4 2008/09/03 300 Hansen

5 2008/08/30 2000 Jensen

6 2008/10/04 100 Nilsen

Nowwewanttofindthetotalsum(totalorder)ofeachcustomer.
WewillhavetousetheGROUPBYstatementtogroupthecustomers. We use the following SQL
statement:

 SELECTCustomer,SUM(OrderPrice)FROMOrders
 GROUPBYCustomer
Theresult-setwilllooklikethis:

Customer SUM(OrderPrice)

Hansen 2000

Nilsen 1700

Jensen 2000

Nowwewanttofindifthecustomers"Hansen"or"Jensen"haveatotalorderofmorethan 1500.
 Having clause
WeaddanordinaryWHEREclausetotheSQLstatement:

 SELECTCustomer,SUM(OrderPrice)FROMOrders
 GROUPBYCustomer

 HAVINGSUM(OrderPrice)>1500

Theresult-setwilllooklikethis

Customer SUM(OrderPrice)

Hansen 2000

Jensen 2000

 GROUP BY More Than One Column

WecanalsousetheGROUPBYstatementonmorethanonecolumn,likethis:
Suppose you have a table called "Sales" with columns "Product," "Region," and "Revenue." To
aggregate data by both "Product" and "Region" and calculate the total revenue for each
combination, you can use the following SQL statement:

In this example, the data is grouped by both "Product" and "Region," and the SUM function
is applied to calculate the total revenue for each group.
 SortingAggregateDatainQueryOutput:
You can sort the aggregate data in the query output using the ORDER BY clause. This
allows you to specify the order in which the results should be displayed.
Use ORDER BY if you want to order rows according to a value returned by an aggregate
function likeSUM(). The ORDER BY operator is followed by the aggregate function (in
our example,SUM()). DESC is placed after this function to specify a descending sort order.
Thus, the highest aggregate values are displayed first, then progressively lower values are
displayed. To sort inascendingorder, you can specifyASC or simplyomit eitherkeyword, as
ascendingis the default sort order.
Example1:
Continuing with the "Sales" table, if you want to see the total revenue for each product and
region combination, sorted in descending order of revenue, you can use the following SQL
statement:

In this example, the data is first aggregated, and then the results are sorted in descending
order of the "TotalRevenue" column.
 FilteringAggregateDataUsingtheHAVINGClause:

The HAVING clause is used to filter the results of aggregate functions. It allows you to specify
conditions that the aggregated data must meet.
SQLHAVINGSyntax

 SELECTcolumn_name,aggregate_function(column_name)

 FROMtable_name

 WHEREcolumn_nameoperatorvalue
 GROUPBYcolumn_name

 HAVINGaggregate_function(column_name)operatorvalue

SQLHAVINGExample1
Wehavethefollowing"Orders"table:

O_Id OrderDate OrderPrice Customer

1 2008/11/12 1000 Hansen

2 2008/10/23 1600 Nilsen

3 2008/09/02 700 Hansen

4 2008/09/03 300 Hansen

5 2008/08/30 2000 Jensen

6 2008/10/04 100 Nilsen

Nowwewant tofindifanyofthecustomershaveatotalorderoflessthan 2000. We use the

SELECTCustomer,SUM(OrderPrice)FROMOrders GROUP
BY Customer
HAVINGSUM(OrderPrice)<2000
following SQL statement:
Theresult-setwilllooklikethis:
Customer SUM(OrderPrice)

Nilsen 1700

Example2:
Supposeyouwanttofindproduct-regioncombinationswithatotalrevenuegreaterthan
$10,000.YoucanusethefollowingSQLstatement:
SELECTProduct,Region,SUM(Revenue)ASTotalRevenue FROM Sales
GROUP BY Product, Region
HAVINGTotalRevenue>10000;
In this example, the HAVING clause filters the results to include only those
combinationswhere the "TotalRevenue" is greater than $10,000.
These SQLstatements demonstrate how to aggregate data, sort the results, and filter
aggregated datausingthe GROUP BY,ORDER BY, and HAVING clauses, respectively.
Theseclausesare essential for summarizing and manipulating data in SQL queries.
1.5. Working with subqueries
1.5.1 Single row subquery
 SQL Sub Queries
A Sub query or Inner query or Nested query is a query within another SQL query and embedded
within the WHERE clause.
A subquery is used to return data that will be used in the main query as a condition to further restrict
the data to be retrieved.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with
the operators like =, <, >, >=, <=, IN, BETWEEN etc.
There are a few rules that subqueries must follow:
 Sub queries must be enclosed within parentheses.
 A sub query can have only one column in the SELECT clause, unless multiple
columns are in the main query
 for the sub query to compare its selected columns.
 An ORDER BY cannot be used in a subquery, although the main query can use
an ORDER BY. The GROUP
 BY can be used to perform the same function as the ORDER BY in a subquery.
 Sub queries that return more than one row can only be used with multiple value
operators, such as the IN operator.
 The SELECT list cannot include any references to values that evaluate to a BLOB,
ARRAY, CLOBor NCLOB.
 A subquery cannot be immediately enclosed in a set function.
 The BETWEEN operator cannot be used with a subquery; however, theBETWEEN
operator can be used within the subquery.Subqueries with the SELECT Statement:
 Subqueries are most frequently used with the SELECT statement. The basic syntax
is as follows:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR