Lec1 Writing Basic SQL SELECT Statements
Uploaded by
pakizaasif0031Lec1 Writing Basic SQL SELECT Statements
Uploaded by
pakizaasif0031Lecture 01
Writing Basic
SQL SELECT Statements
Objectives
After completing this lecture, you should be able to do
the following:
• Understanding of Basic SQL Statements.
• Using different arithmetic operators inside SQL
statements for data retrieval.
SQL
• SQL stands for Structured Query Language
• SQL lets you access and manipulate databases
BASIC SQL COMMANDS
There are a number of SQL statements, few of which are
explained below:
• Data Retrieval Statement: SELECT is the data extracting
statement which retrieves the data from the database.
• Data Manipulation Language (DML): This language
constitutes the statements that are used to manipulate with
the data. It has three commands, which are INSERT,
UPDATE and DELETE.
• Data Definition Language (DDL): This is the language
used to define the tables. It sets up, changes, and removes
data structures from the tables. It uses 5 commands, which
are CREATE, ALTER, DROP, RENAME and TRUNCATE.
Writing SQL Statements
• SQL statements are not case sensitive.
• SQL statements can be on one or more lines.
• Keywords cannot be abbreviated or split across
lines.
• Clauses are usually placed on separate lines.
• Indents are used to enhance readability.
Basic SELECT Statement
• SELECT identifies what columns
• FROM identifies which table
In the syntax:
KEYWORD MEANING
SELECT is a list of one or more columns
* selects all columns
DISTINCT suppresses duplicates
column|expression selects the named column or the
expression
alias gives selected columns different
headings
FROM table specifies the table containing the
columns
Selecting All Columns
Selecting Specific Columns
Arithmetic Expressions
Create expressions with number and date data by
using arithmetic operators.
Using Arithmetic Operators
Note:
Resultant calculated column SALARY+300 is not a new column in the
EMPLOYEES table; it is for display only. By default, the name of a new column
comes from the calculation that generated it— in this case, salary+300.
Operator Precedence
• Multiplication and division take priority over addition
and subtraction
• Operators of the same priority are evaluated from left
to right
• Parentheses are used to force prioritized evaluation
and to clarify statements
Operator Precedence
Using Parentheses
Defining a Null Value
• A null is a value that is unavailable, unassigned,
unknown, or inapplicable
• A null is not the same as zero or a blank space
Null Values
in Arithmetic Expressions
Arithmetic expressions containing a null value
evaluate to null.
Null VALUES (Cont..)
• If any column value in an arithmetic expression
is null, the result is null.
• For example, if you attempt to perform division
with zero, you get an error.
• However, if you divide a number by null, the
result is a null or unknown.
Defining a Column Alias
A column alias:
• Renames a column heading
• Is useful with calculations
• Immediately follows the column name: there can
also be the optional AS keyword between the
column name and alias
• Requires double quotation marks if it contains
spaces or special characters or is case sensitive
Using Column Aliases
Concatenation Operator
A concatenation operator:
• Concatenates columns or character strings to
other columns
• Is represented by two vertical bars (||)
• Creates a resultant column that is a character
expression
Using the Concatenation Operator
Literal Character Strings
• A literal value is a character, a number, or a
date included in the SELECT list
• Date and character literal values must be
enclosed within single quotation marks
• Each character string is output once for each
row returned
Using Literal Character Strings
Using Literal Character Strings
Duplicate Rows
The default display of queries is all rows, including
duplicate rows.
Eliminating Duplicate Rows
Eliminate duplicate rows by using the DISTINCT
keyword in the SELECT clause.
Displaying Table Structure
View OBJECTS AND tables in
database
• To view the objects in the database
SELECT * FROM CAT;
• To view the tables in the database
SELECT * FROM TAB;
Q/A