Structured Query Language

ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 1
Copyright © 2015 – 2018, Victorian Institute of Technology.
The contents contained in this document may not be reproduced in any form or by any means, without the written permission of VIT,
other than for the purpose for which it has been supplied. VIT and its logo are trademarks of Victorian Institute of Technology.
STRUCTURED QUERY LANGUAGE
AND
SELECT STATEMENT
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 2
Topics
• Structured Query Language
• SQL Server Management Studio
• Select Statement
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 3
Structured Query Language
• SQL is a tool for organizing, managing, and retrieving data
stored by a computer database. The acronym SQL is an
abbreviation for Structured Query Language.
• For historical reasons, SQL is usually pronounced “sequel,”
but the alternate pronunciation “S.Q.L.” is also used.
• As the name implies, SQL is a computer language that you
use to interact with a database. In fact, SQL works with one
specific type of database, called a relational database.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 4
Structured Query Language (Cont…)
SQL
Request
Data or
Information
Database Management
System Database
• 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.
• This process of requesting data from a database and receiving back the results is
called a database query—hence the name Structured Query Language.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 5
Structured Query Language (Cont…)
• SQL is used to control all of the functions that a DBMS
provides for its users, including:
– Data definition: SQL lets a user define the structure and organization of
the stored data and relationships among the stored data items.
– Data retrieval: SQL allows a user or an application program to retrieve
stored data from the database and use it.
– Data manipulation: SQL allows a user or an application program to
update the database by adding new data, removing old data, and
modifying previously stored data.
– Access control. SQL can be used to restrict a user’s ability to retrieve,
add, and modify data, protecting stored data against unauthorized access.
– Data sharing. SQL is used to coordinate data sharing by concurrent
users, ensuring that they do not interfere with one another.
– Data integrity. SQL defines integrity constraints in the database,
protecting it from corruption due to inconsistent updates or system failures.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 6
Structured Query Language (Cont…)
• Role of SQL
Component of a typical database management system
Forms
Facility
Report
Writer
Interactive
Query Tool
Application
Program
Database
Front-end
Database Engine
Database
SQL
To other
Computer System
SQL
Database Gateway
To other DBMS Brand
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 7
Structured Query Language (Cont…)
• Role of SQL
– SQL is an interactive query language: Users type SQL commands
into an interactive SQL program to retrieve data and display it on the
screen.
– SQL is a database programming language. Programmers embed
SQL commands into their application programs to access the data in
a database.
– SQL is a database administration language. The database
administrator responsible for managing a minicomputer or mainframe
database uses SQL to define the database structure and control
access to the stored data.
– SQL is a client/server language. Personal computer programs use
SQL to communicate over a network with database servers that
store shared data.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 8
Structured Query Language (Cont…)
• Role of SQL
– SQL is an Internet data access language. Internet web servers that
interact with corporate data and Internet applications servers all use
SQL as a standard language for accessing corporate databases.
– SQL is a distributed database language. Distributed database
management systems use SQL to help distribute data across many
connected computer systems.
– SQL is a database gateway language. In a computer network with a
mix of different DBMS products, SQL is often used in a gateway that
allows one brand of DBMS to communicate with another brand.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 9
Structured Query Language (Cont…)
• SQL Statements
– SQL statements are divided into two major categories: data
definition language (DDL) and data manipulation language
(DML).
– Data Definition Language
• DDL statements are used to build and modify the structure of
your tables and other objects in the database. When you execute
a DDL statement, it takes effect immediately.
• Few list of SQL Statements
– Create, Alter and Drop
– Data Manipulation Language
• DML statements are used to work with the data in tables.
• Few list of SQL Statements
– Select, Insert, Update and Delete
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 10
Structured Query Language (Cont…)
• So, What can SQL do?
– SQL can execute queries against a database
– SQL can retrieve data from a database
– SQL can insert records in a database
– SQL can update records in a database
– SQL can delete records from a database
– SQL can create new databases
– SQL can create new tables in a database
– SQL can create stored procedures in a database
– SQL can create views in a database
– SQL can set permissions on tables, procedures, and views
Note: Most of the above functionality of SQL, will be taught within few lessons
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 11
SQL Server Management Studio
• To open Server Management
Studio
– Click on ‘Start’ button and
select ‘SQL Server 2014
Management Studio’.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 12
SQL Server Management Studio (Cont…)
– Click on ‘Connect’ to start Microsoft SQL Server 2014
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 13
SQL Server Management Studio (Cont…)
• Click on “New Query” and below screen will appear.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 14
SQL Server Management Studio (Cont…)
• Copy-paste the script of Northwind database in new query
window and Execute it.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 15
SQL Server Management Studio (Cont…)
• Refresh the database and you will see a Northwind
database.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 16
SQL Server Management Studio (Cont…)
– We will deal with Northwind database.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 17
SQL Server Management Studio (Cont…)
– If, Northwind database is not there in ‘Object Explorer’ then
restore the Northwind database by right clicking the folder
‘Databases’ in ‘Object Explorer’ and select Restore.
• Restore Database window will open.
– Select Device in Source section and press the button on its right,
which will open Select backup devices window.
» Press ADD button in Select backup devices window, which
will open Locate Backup File window.
» Browse the backup file of Northwind database and
select it, which will attach the Northwind database
that can be use.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 18
SQL Server Management Studio (Cont…)
• Northwind Database Schema
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 19
SQL Server Management Studio (Cont…)
• To write SQL statements select New Query from Toolbar of
Microsoft SQL Server Management Studio.
– After writing the query you can execute it by pressing Execute option
on Toolbar of Microsoft SQL Server Management Studio.
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 20
Select Statement
• Syntax (initially we can start with below syntax)
– SELECT column1, column2, …
FROM table_name;
Here, column1, column2 are the field names of the table you want
to select data from. If you want to select all the fields available in
the table, use the following syntax:
– SELECT * FROM table_name;
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 21
Select Statement (Cont..)
• Example
– SELECT * FROM Customers
• Output
Note: Many other fields are there but not displaying here
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 22
Select Statement (Cont..)
• Example
– SELECT CompanyName, ContactTitle FROM Customers
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 23
Select Statement (Cont..)
• Example
– SELECT DISTINCT Country FROM Customers;
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 24
Select Statement (Cont..)
• Example
– SELECT COUNT(DISTINCT Country) FROM Customers;
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 25
Select Statement (Cont..)
• Syntax
– SELECT column1, column2, …
FROM table_name
WHERE condition;
• Example
– SELECT * FROM Customers WHERE ContactTitle=’Owner’;
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 26
Select Statement (Cont..)
• SQL requires single quotes around text values. However,
numeric fields should not be enclosed in quotes
• Example
– SELECT * FROM Products where UnitsInStock = 39
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 27
Select Statement (Cont..)
• Operators in WHERE Clause
– The following operators can be used in the WHERE clause:

ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 28
Select Statement (Cont..)
• Example
– SELECT * FROM Products where UnitsInStock > 39
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 29
Select Statement (Cont..)
• Using and with where
• Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition1 AND condition2 AND condition3 …;
• Example
– SELECT * FROM Products where UnitsInStock > 39 and
UnitsInStock <= 42
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 30
Select Statement (Cont..)
• Using or with where
• Syntax
SELECT column1, column2, …
FROM table_name
WHERE condition1 OR condition2 OR condition3 …;
• Example
– SELECT * FROM Products where UnitsInStock > 50 or UnitsInStock = 40
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 31
Select Statement (Cont..)
• Using Not with where
• Syntax
SELECT column1, column2, …
FROM table_name
WHERE NOT condition;
• Example
– SELECT * FROM Products where not UnitsInStock > 3
• Output
ITDA1001 [Lesson 5] Copyright © 2018 VIT, All Rights Reserved 32
Summary
• Revision of Key Concepts
• Lab Work
– Lesson 5 – Activity 5 (Lesson_5 Activity.docx)
• Questions and Answer

The post Structured Query Language appeared first on My Assignment Online.

Expert Help With This Assignment — On Your Terms