SQL Programming Knowledge

SQL Programming Knowledge Upon completion of this course you will know: The general rules of SQL Syntax Operators SQL Functions SQL Clauses Different types of SQL JOINS Basic SQL Commands The difference between Tables and Views SQL Security Commands Different types of Indexes Different types of SQL syntax errors Different types of SET commands Dynamic uses of SQL Skills Upon completion of this course, you will know: Build a block of data retrieval Change the order of a column Indent code Execute JOIN with SQL Clauses Execute Embedded SELECT statements with Nested queries Execute INSERT, UPDATE and SELECT statements Create Views Create Indexes on SQL tables Control transactions Knowledge Upon completion of today’s session you will: Know the general rules of SQL Syntax Know the operators Be familiar with SQL Functions Skill Upon completion of today’s session, a student will be able to: Build a block of data retrieval Change the order of a column Indent code Introduction to Query, Expressions, and Types of SQL commands SQL is standard language for accessing and manipulating databases. It stands for Structured Query Language SQL is an ANSI (American National Standards Institute )standard. 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 Download Microsoft SQL Server from Microsoft website. Microsoft SQL Server has free editions called express editions. Some downloads of SQL server have Management studio contained within it. If you already have SQL server installed, you need to find and download only SQL server management studio. SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide developers and administrators of all skill levels access to SQL Server. This release features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available. Provide settings that you chose at installation like server name and password to connect to sql server. If it is already running get these from your administrator. A database can be created, altered and dropped: 1. GUI(graphical user interface):Graphically using SQL Server Management Studio (SSMS) or 2. T-SQL: Using a Query To create the database graphically 1. Right Click on Databases folder in the Object explorer 2. Select New Database 3. In the New Database dialog box, enter the Database name (for example db1)and click OK. To Create the database using a query Create database DatabaseName To alter a database, once it's created  Alter database DatabaseName Modify Name = NewDatabaseName To Delete or Drop a database Drop Database That You Want To Drop The database itself The transaction log: contains a log of all work done with database and by who Tables: Contain that actual data in form of rows Indexes: Contain rules about how data will be stored and File groups: Primary and Secondary Filegroups. A primary file group contains the primary datafile (mdf) and possibly secondary datafiles (ndf). All system tables are allocated to the primary filegroup. A secondary file group (also called a user-defined filegroup) contains secondary datafiles (ndf) and database objects. Diagrams: Shows how tables are linked together Views: show set of information retrieved by a query(read only mostly) Stored Procedures: functions stored within SQL server to perform different tasks User-defined functions: Any functions defined by user to perform various tasks Users: People who will use database information Roles: types of users like entry, maintenance Reports: set of information generated User-defined data types: data types created as needed in addition to built in data types 1.DDL DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database. Examples: CREATE, ALTER, DROP statements 2.DML DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements 3.DCL DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it. Examples: GRANT, REVOKE statements 4.TCL TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK statements Introduction to DDL DDL stands for Data Definition Language. It is a language used for defining and modifying the data and its structure. It is used to build and modify the structure of your tables and other objects in the database. DDL commands are as follows, 1. CREATE 2. DROP 3. ALTER 4. RENAME 5. TRUNCATE These commands can be used to add, remove or modify tables within a database. CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers). DROP – is used to delete objects from the database. ALTER-is used to alter the structure of the database. TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed. COMMENT –is used to add comments to the data dictionary. RENAME –is used to rename an object existing in the database. Exact Numeric Data Types: int: Integer data type for whole numbers. smallint: Smaller integer data type. tinyint: Very small integer data type. bigint: Large integer data type. numeric(p, s): Fixed-point numeric data type with a specified precision (p) and scale (s). decimal(p, s): Same as numeric, used for fixed-point numbers. bit: Represents Boolean values (0 or 1). money and smallmoney: Monetary values. Approximate Numeric Data Types: float: Approximate numeric data type for floating-point numbers. real: Smaller version of float. Character String Data Types: char(n): Fixed-length character strings. varchar(n): Variable-length character strings with a maximum length (n). text: Variable-length character strings for large amounts of text. 4. Unicode Character String Data Types: nchar(n): Fixed-length Unicode character strings. nvarchar(n): Variable-length Unicode character strings with a maximum length (n). ntext: Variable-length Unicode character strings for large amounts of text. 5. Binary Data Types: binary(n): Fixed-length binary data. varbinary(n): Variable-length binary data with a maximum length (n). image: Variable-length binary data for large amounts of binary data. 6. Date and Time Data Types: datetime: Date and time values with fractional seconds accuracy. date: Date values without time. time: Time values without date. datetime2: Date and time values with higher fractional seconds accuracy. smalldatetime: Date and time values with reduced fractional seconds accuracy. To create tblPerson table, graphically: 1. Right click on Tables folder in Object explorer window 2. Select New Table 3. Fill Column Name, Data Type and Allow Nulls, as shown below and save the table as tblPerson. The following statement creates tblGender table, with ID and Gender columns. ID column, is the primary key column. The primary key is used to uniquely identify each row in a table. Primary key does not allow nulls. Create Table tblGender (ID int Not Null Primary Key, Gender nvarchar(50)) SELECT - This command is used to retrieve information from a table 2) INSERT - This command is used to add information to a table 3) UPDATE - This command is used to modify information to a table 4) DELETE - This command is used to remove information from a table What is the difference between Delete and Truncate? 1. Delete and Truncate commands remove data from existent tables in a database without harming the table structure or other references to the table. 2. However, Delete command can be used to delete specific rows only in a table using a relevant condition, or to delete all the rows without any condition, while the Truncate command can be used only for deleting entire data in the table. 3. Delete is a DML command, and it can roll back the operation if necessary, but Truncate is a DDL command, so it is an auto commit statement and cannot  be rolled back in any way. So it is important to use this command carefully in database management. 4. Truncate operation consumes fewer system resources and transaction log resources than the Delete operation, therefore, Truncate is considered as faster than Delete. 5. Also, Delete does not deallocate space used by the table, whereas Truncate frees the space used after execution, so Delete is not efficient in case of deleting the entire data from a database table. 6. However, Truncate is not allowed to use when the table is referenced by a foreign key constraint, and in that case, the Delete command can be used instead of Truncate. 7. Finally, both of these commands have advantages and also disadvantages in applying them in Database Management Systems and the user should be aware of using these commands appropriately to achieve good results.   

equal : =

not equal: : != or <>

Note: to see the line numbers: Tools ==>Options...==>Text Editor==>All languages==>General==>Check Line numbers==>ok

Note: If you want to do any changes but you are not allowed you can : 

Tools==> Options...===>Designers==>uncheck Prevent saving changes that require table re creation

Database

Create Database

Method #1:

You can create a database by using GUI(Graphical User Interface):

Object Explorer==>Databases==>Right click==> New Database...==> type a name ==> Ok

Method #2:

You can create a database by using T SQL

click on the New Query icon then write the following commands highlight them and press F5 or 

Press Execute button

create database dbtest1; ; in SQL is optional

Create database db2

create database db3

alter database db3 modify name=db3_test

drop database db2

Note: if you want to see this new database go to the Object Explorer window, select Databases, and then 

Press Refresh icon

New version of SQL is not case sensitive for syntax and names of tables and databases and columns, it is case sensitive just 

for values inside tables

Create DataBASE dbMaryam;

go go is optional, and you can use it at the end of each batch to separate batches 

Drop/delete Database

Method #1: by using GUI: Object Explore==> expand Databases==> Choose the name of database that you

want to delete then R.C. on it ==> Delete==>ok

Method #2:By using T SQL

drop database dbtest1

go

Ex: create a new database called dbnew

create database dbnew

go

change name of Database

change name of dbnew to dbnew2 or dbnew2 to dbnew3

Method #1: by using GUI: In Object Explorer R.C. on dbnew and Select Rename then type dbnew2

Method #2: by using T SQL: 

Alter database dbnew modify name=dbnew1

Ex: drop dbnew1 by using T SQL

drop database dbnew1

Ex: Again create a new database called dbtest1

create database dbtest1

change working database

change your working database from master to dbtest1

Method#1:GUI: above Object Explorer you can see the master press arrow and then select dbtest1

Method#2:T SQL

create database mydb

use mydb

Types of SQL commands:

SQL DML, DDL, DCL, and TCL Commands

1.

*SQL DDL Commands*

In SQL, DDL means Data Definition Language. The Sql Server DDL commands are used to create 

and modify the structure of a database and database objects like tables, views, procedures,....

Examples of Sql Server DDL commands are

CREATE – Create an object. I mean, create a database, table, triggers, index, functions, stored procedures, etc.

DROP – This SQL DDL command helps to delete objects. For example, delete tables, delete a database, etc.

ALTER – Used to alter the existing database or its object structures.

TRUNCATE – This SQL DDL command removes records from tables

RENAME – Renaming the database objects

2.

*SQL DML Commands*

DML means Data Manipulation Language in Sql Server. As its name suggests, these Sql Server DML 

commands will perform data manipulation( manipulate data presented in the server).

Examples of DML commands in SQL Server are

SELECT – This SQL DML command selects records or data from a table

INSERT – Insert data into a database table.

UPDATE – This SQL DML command will update existing records within a table

DELETE – Delete unwanted records from a table 

Difference between delete and truncate: 

DELETE:

    DELETE is a DML (Data Manipulation Language) command.

    It is used to remove specific rows from a table based on a condition you provide.

    You can use DELETE with a WHERE clause to specify which rows to delete.

  

   

    TRUNCATE is a DDL (Data Definition Language) command.

    It is used to remove all the rows from a table quickly, effectively resetting the table.

    TRUNCATE doesn't use a WHERE clause because it removes all rows by default.

3.

*SQL DCL Commands*

The Sql Server DCL means Data Control Language. These DCL commands in SQL Server will control 

the Data access permission. 

Sql Server DCL commands Examples are

GRANT – It permits users to access the database.

REVOKE – This SQL DCL command withdraws the permission given by GRANT to access the database.

4.

*SQL TCL Commands*

TCL means Transaction Control Language. These Sql Server TCL commands will control the Transactions..

Examples of TCL commands in SQL Server are

COMMIT –  This SQL TCL command will commit the running transaction

ROLLBACK – Rollback(Undo) the current transaction 

SAVEPOINT – You can set a save point so that, next time it will start from that point

SET TRANSACTION – Specify the characteristics of the transactions

Create Table

Method #1:Using GUI: Object Explorer==> Choose the name of the database that you want to create a table

inside it==>expand it by press plus sign==>R.C. on Tables==>New==>Table...

type column name, data type, and check or uncheck Allow Nulls and continue for all columns

after that Ctrl+S to save it and name table then refresh Object Explorer and follow the slides

page 26

make sure you assign same data type for GenderId in tblperson and Id in tblGender

go

alter table tblperson

add constraint FK_tblperson_tblgender foreign key (genderid) references tblgender(id)

go

for insert data by using GUI:

RC(Right Click) on table from object explorer select Edit top 200 rows

for print data:

RC(Right Click) on table from Object Explorer select  top 1000 rows 

Method#2: Using T SQL

retrieve data: for printing all records in tblPerson

select * from tblPerson * means all columns

or

select * from [tblPerson]

or

select * from dbo.tblPerson

0r

select * from [dbo].[TblPerson]

SELECT TOP 3 * FROM tblPerson;

go

Note: If you want to just print some particular columns you should specify the name of those columns

one column

Ex: Retrieve name of people in tblperson

select name from tblPerson

Ex: Retrieve name and email of people in tblperson

more than column: use , between name of columns

select name , EMail from TblPerson

Alias: SQL aliases are used to give a table, or a column in a table, a temporary name.

select name as Name_Of_people from tblPerson as is optional and Name_Of_people is alias

or

select name  Name_Of_people from tblPerson 

or

select name  [Name_Of_people] from tblPerson 

if you want to use space in name you have to use sqaure brackets

select name  [Name Of people] from tblPerson 

select name as [Name of Student] , email as [Email of Student] from tblPerson

or

select name [Name of Student] , email  [Email of Student] from tblPerson

go

Note: If you want to do any changes but you are not allowed you can :

Tools==> Options...===>Designers==>uncheck Prevent saving changes that require table re creation

use 

Creating tables by using T SQL

create table tblPerson1 

(ID int not null,

fname nvarchar(50) not null,

lname nvarchar(50))

go

Insert data(rows)(records)(observations)

Note: for categorical columns, you must wrap their values inside single quotes

Note: In SQL you must wrap characters by using single quotation marks, not double quotation marks

inserting one record 

insert into tblPerson1 values

(1,'Maryam','Kazemi')

inserting more than one record: 

you can use the insert command for each record or use one insert command for all of them

If you use one insert for several records you should wrap each record in parentheses and 

separate records by comma

insert into tblPerson1 values

(2,'Adil','Fagiri'),

(3,'Bachittar','Dhaliwal'),

(4,'Gurdeep','Sandhu')

retrieve data

Select * from tblPerson1

go

Primary Key

primary key :The PRIMARY KEY constraint uniquely identifies each record in a table

1.has to be unique and 

2.not empty(not null)

(Primary keys must contain UNIQUE values, and cannot contain NULL values)

3.A table can have at most only ONE primary key; and in the table, 

this primary key can consist of single or multiple columns (fields).

Note: We can assign P.K. to one table during the creation of that table or after creating it, but

 Be careful after creating and inserting some records, you cannot assign pk to the column that 

 has duplicated values or has missing values because a primary key 

1.has to be unique and 

2.not empty(not null)

Create a primary key for existing table

assigning pk to existing table:

Method#1: GUI: R.C. on that table from Object Explorer ==>Design==> 

The left side of that column you can :

set P.K. or remove P.K.

Note: make sure that column doesn't have duplicated values or Missing values

Note: If you want to do any changes but you are not allowed you can : 

Tools==> Options...===>Designers==>uncheck Prevent saving changes that require table re creation

Method#2: T SQL:

alter table tblperson1

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) and It must

have unique values

Note: I will tell you how to assign a specific name to P.K.

in this example the name of p.K. was assigned by SQL and you can find it in Object Explorer by 

finding the name of the table then expand the Keys

Drop Pk by using T SQL

alter table tblperson1

drop constraint[PK__tblPerso__3214EC27BE671F31] you should drag and drop the name of P.K here from Object explorer

you can find the name of pk by Object Explore==>expand the table==> expand Keys

  or you can name your pk when you add it in the alter command

Assign a specific name for the PRIMARY KEY constraint

ALTER TABLE tblPerson1

ADD CONSTRAINT pk_tblperson_ID PRIMARY KEY (ID); pk_tblperson_ID is the name that i wanted to assign to P.K

Drop Pk by using T SQL

alter table tblperson1

drop constraint pk_tblperson_ID

Assign a name for PRIMARY KEY constraint, and defining a PRIMARY KEY constraint on multiple columns

ALTER TABLE tblPerson1

ADD CONSTRAINT pk_tblperson_ID_and_fname PRIMARY KEY (ID,fName);

I used CONSTRAINT pk_tblperson_ID_and_fname before primary key keyword to assign a name 

to my primary key

pk_tblperson_ID_and_fname is the name of Pk

ALTER TABLE tblPerson1

DROP CONSTRAINT pk_tblperson_ID_and_fname;

Assigning PK during creation of table

create table tblGender2

(Id int Not null primary key, not null is optional because Pk by default can't be null

Gender nvarchar(20))

create table tblGender3

(Id int  primary key, not null is optional because Pk by default can't be null

Gender nvarchar(20))

In two above example the name of p.K. were assigned by sql if you want to assign specific name you should do similar to code bellow

create table tblgender_4

(id int not null,

Gender nvarchar(20),

constraint PK_tblgender_4_Id primary key(id))

CREATE TABLE Employee (

ID int NOT NULL,

Employee_name varchar(255) NOT NULL,

Employee_designation varchar(255),

Employee_Age int,

UNIQUE(ID)

);

select * from Employee

drop a table

Method #1: using GUI :

Method#2:using T SQL

drop table tblGender3

select * from tblGender3 You will get an Error because tblGender3 does not exist any more

go

Note: in T_sql there is a difference between deleting a table and dropping a table:

when you delete a table by T SQL (delete statement) you just remove all records of that table 

not the structure of the table, you just making that table empty

But by dropping a table you remove that table object and all its records from the database

delete tblperson

select * from tblperson

drop table tblperson

select * from tblperson You will get an error because that tblperson is not exist anymore

create table tblperson

(Id int not null primary key, not null is optional :According to the SQL Specification, a primary key can not contain NULL. This means that decorating a column with either "NOT NULL PRIMARY KEY" or just "PRIMARY KEY" should do the same thing.

name nvarchar(50),

email nvarchar(50),

genderid int) if you want to refer this genderid to id in tblgender(FK) you must use  the same data type

drop tblperson

drop table tblperson

Assigning a name to the primary key during the creation of the table

Note: the name of Pk is a random name that Sql was created for it 

If you want to assign any name you should do like following code

create table tblperson

(Id int not null, not null is optional: According to the SQL Specification, a primary key cannot contain NULL. This means that decorating a column with either "NOT NULL PRIMARY KEY" or just "PRIMARY KEY" should do the same thing.

name nvarchar(50),

email nvarchar(50),

genderid int,

Constraint pk_tblperson_ID primary key(ID))

go

Create a database and name it dbtest2

create database dbtest2

use dbtest2

Please take a look to page 26 in slides of Lecture 1 

create table tblperson

(Id int , not null is optional : According to the SQL Specification, a primary key can not contain NULL. This means that decorating a column with either "NOT NULL PRIMARY KEY" or just "PRIMARY KEY" should do the same thing.

name nvarchar(50),

email nvarchar(50),

genderid int, if you want to refer this genderid to id in tblgender(FK) you must use  the same data type

Constraint pk_tblperson_ID primary key(ID))

create table tblGender

(Id int Not null primary key,

Gender nvarchar(20))

A FOREIGN KEY

A foreign key is a column or group of columns in a relational database table that provides 

a link between data in two tables.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to 

another column(usually primary key) in another table.

The table containing the foreign key is called the child table, and the table containing the 

candidate key is called the referenced or parent table.

A foreign key can refer to either a unique or a primary key of the parent table. 

If the foreign key 

refers to a non primary unique key, you must specify the column names of the key explicitly.

F.k.  Vs.  P.K.

The PRIMARY KEY constraint uniquely identifies each record in a table like studentId

(its values must be unique and can not be missing)

A FOREIGN KEY is a field (or collection of fields) in one table that refers to 

another column(usually primary key) in another table

1. unlike PK we can have more than one FK

2.unlike PK  , FK can have missing values

3.unlike PK  , FK can have duplicated values

Assigning FK to an existing table

Assigning FK to genderid in tblperson reffer it to id in tblgender

Object Explorer ==>tblgender==>Keys 

For viewing Dependency :

Object Explorer ==>tblgender==>RC ==>view Dependencies

For creating  Database diagram :

Object Explorer ==>expand dbtest2==>Database Diagrams==>R.C ==>New Database Diagram

press Ctrl and select two tables ==>add

Like P.K. If you remove constraint FK_tblperson_tblgender SQL will assign a random name for F.K.

ALTER TABLE tblperson

ADD FOREIGN KEY (genderid) REFERENCES tblgender(id);

Assigning FK during creation of table

Assigning FK to gendercode in tblperson2 refer it to id in tblgender

method #1:allowing sql to select a random name

create table tblperson3

(id int  primary key,

name nvarchar(50),

email nvarchar(50),

genderid int references tblgender(id)) here Sql will assign names for Pk and FK

go

**************** method #2:assigning a specific name to F.K or P.K ******************

create table tblperson4

(id int not null,

name nvarchar(50),

email nvarchar(50),

genderid int,

constraint PK_tblperson4 primary key(id),

constraint FK_tblPerson4_tblGender_ID foreign key (genderid) references tblGender(Id)) 

go

Interview Question: What are the differences between P.K. and F.K.?

The PRIMARY KEY constraint uniquely identifies each record in a table.

primary key has to be unique and not empty(not null) and a table can have at most only ONE primary key; 

and in the table, this primary key can consist of single or multiple columns (fields).

A FOREIGN KEY is a field (or collection of fields) in one table that refers to 

another column(usually primary key) in another table and unlike P.k. it can be null and have duplicated

values and we can have several FKs in one table

The table containing the foreign key is called the child table, and the table containing the candidate key 

is called the referenced or parent table

insert data to table

insert into tblGender values

(1,'male'),(2,'female'),(3,'unknown')

insert into tblperson3 values

(1,'Maryam','maryamkazemi@yahoo.com',2),

(2,'Mary','m@mcoleg',3)

select

select * from tblperson3 * means all columns

select * from tblGender 

select name from tblperson3

select name as [first name] from tblperson3 as is optional 

select name [first name] from tblperson3

select name ,email from tblperson3 you have to put "," between columns' name

select name  [first name],email personal_email from tblperson3 

select name fullname,email [personal email] from tblperson3 use hard bracket if you want to have space or special charechters

select * from tblGender

delete

Delete contents of a table= make a table empty not drop that table

delete tblperson

select * from tblperson

insert into tblperson values

(1,'Maryam','maryamkazemi@yahoo.com',2),

(2,'Mary','m@mcoleg',3)

select * from tblperson3  remind me to show dependency diaGRam)

The following example creates a table named T1. Then the second statement drops the table. 

The third statement performs no action because the table is already deleted, however it 

does not cause an error.

use dbtest1

go

CREATE TABLE T1 (Col1 int);

GO  

Drop table T1

Drop table T1 since T1 was dropped we got an ERROR because it does not exist any more

GO  

DROP TABLE IF EXISTS T1;  IF EXISTS does not work in some version of SQL

DAY 2