sql presentatioon day3

8/6/2019 SQL Presentatioon Day3

1/31

Presented By Aptech Training Solutions

Faculty : Naveen Mishra


2/31

SQL Views

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the

result-set of an SQL statement.

A view contains rows and columns, just like areal table. The fields in a view are fields from

one or more real tables in the database.

You can add SQL functions, WHERE, and JOINstatements to a view and present the data as

if the data were coming from one single table.


3/31

SQL CREATE VIEW Syntax

CREATE VIEW view_name ASSELECT column_name(s)FROM table_nameWHERE condition

CREATE VIEW [Current Product List] AS

SELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=No


4/31

We can query the view above as follows:

SELECT * FROM [Current Product List]

Another view in the Northwind sample databaseselects every product in the "Products" table

with a unit price higher than the average unit

price:


5/31

CREATE VIEW [Products Above Average Price] AS

SELECT ProductName,UnitPrice

FROM Products

WHERE UnitPrice>(SELECT AVG(UnitPrice)

FROM Products)

SELECT * FROM [Products Above Average Price]


6/31

SQL Dropping a View

You can delete a view with the DROP VIEW

command.

SQL DROP VIEW SyntaxDROP VIEW view_name


7/31

SQL ALTER TABLE Statement

The ALTER TABLE Statement

The ALTER TABLE statement is used to add,

delete, or modify columns in an existing table.SQL ALTER TABLE Syntax

To add a column in a table, use the following

syntax:ALTER TABLE table_name

ADD column_name datatype


8/31

To delete a column in a table, use the

following syntax (notice that some database

systems don't allow deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name

To change the data type of a column in a

table, use the following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name datatype


9/31

Now we want to add a column named

"DateOfBirth" in the "Persons" table.We use the following SQL statement:

ALTER TABLE Persons

ADD DateOfBirth datetime


10/31

SQL CREATE INDEX Statement

The CREATE INDEX statement is used to create

indexes in tables.

Indexes allow the database application to finddata fast; without reading the whole table.

An index can be created in a table to find data

more quickly and efficiently.

The users cannot see the indexes, they are just

used to speed up searches/queries.


11/31

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values

are allowed:

CREATE INDEX index_nameON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicatevalues are not allowed:


12/31

CREATING UNIQUE INDEX

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

If you want to create an index on a

combination of columns, you can list thecolumn names within the parentheses,

separated by commas:

CREATE INDEX PIndexON Persons (LastName, FirstName)


13/31

Drop Index /Table

The DROP INDEX Statement

The DROP INDEX statement is used to delete an

index in a table.DROP INDEX table_name.index_name

The DROP TABLE Statement

The DROP TABLE statement is used to delete atable.

DROP TABLE table_name


14/31

DROP DATABASE

The DROP DATABASE Statement

The DROP DATABASE statement is used to

delete a database.DROP DATABASE database_name


15/31

TRUNCATE TABLE

The TRUNCATE TABLE Statement

What if we only want to delete the data inside

the table, and not the table itself? Then, use the TRUNCATE TABLE statement:

TRUNCATE TABLE table_name


16/31

Creating Login

To create a SQL Server

login that uses SQL

Server Authentication

(SQL ServerManagement Studio)


17/31

In SQL Server Management Studio, open ObjectExplorer and expand the folder of the server instance

in which to create the new login. Right-click the Security folder, point to New, and then

click Login.

On the General page, enter a name for the new login inthe Login name box.

Select SQL Server Authentication. WindowsAuthentication is the more secure option.

Enter a password for the login.

Select the password policy options that should be

applied to the new login. In general, enforcingpassword policy is the more secure option.

Click OK.


18/31

There are steps how to GENERATE SQL SCRIPT in

SQL server 2000 Right click on the temporary

data and pointing all tasks

Select Generate SQL Script, in the dialogue boxclick show all.

under script all objects, check the box of all tables

Click options, under table scripting options, check

the box of Script indexes, and Script primary keys,

FOREGIN keys, defaults, and check constraints.

Generating SQL Scripts


19/31

Generating SQL Scripts


20/31


21/31

Back up a database


22/31

Back up a database

After connecting to the appropriate instance of the Microsoft SQLServer Database Engine, in Object Explorer, click the server name toexpand the server tree.

Expand Databases, and depending on the database, either select auser database or expand System Databasesand select a system

database. Right-click the database, point to Tasks, and then click Back Up.

The Back Up Database dialog box appears.

In the Database list box, verify the database name. You canoptionally select a different database from the list.

You can perform a database backup for any recovery model(FULL, BULK_LOGGED, or SIMPLE).

In the Backup type list box, select Full.

Note that after creating a full database backup, you can create adifferential database backup; for more information


23/31

Back up a database


24/31

Restore Database

Right-click the database, point to Tasks, and

then click Restore.

ClickD

atabase, which opens the RestoreDatabase dialog box.

On the General page, the name of the

restoring database appears in the To

database list box. To create a new database,

enter its name in the list box.


25/31

Restore Database


26/31

Restore Database


27/31

Output an SQL Query to a text file

EXEC master.dbo.sp_configure 'show advanced

options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell',

1

RECONFIGURE


28/31

EXEC master..xp_cmdshell'bcp "SELECT TOP 5

CUSTOMERID FROM

Northwind.dbo.Customers" queryout

"C:\Users\Naval\Desktop\SQL

Assignments\text.txt" -c -T -x'

It will generate the output in destination

folder as text.txt


29/31

Output an SQL Query to any file


30/31

Ctrl + Shift + f Output to File

Ctrl +d -> Output to SQL Grid


31/31

Formatted Output

sql presentatioon day3

Documents