Lec-w9-SQL.pptx Introduction to SQL in basics

An SQL Lecture on the Introduction to SQL

1. Database Engineering Week 9 Introduction to SQL Ch-6: Modern Database Management Systems (10 th ED) By Dr Farooque Azam 1 SQL Script download Link: https://wps.prenhall.com/bp_hoffer_edm_1/238/61126/15648380.cw/content/index.html

2. 2 Agenda You will know: - Data definition language ( DDL ), Data manipulation language ( DML ), Data control language ( DCL ),

3. 3 SQL Overview Structured Query Language-SQL is used to create, modify , and query relational databases that store data in tables with rows and columns SQL is a standard language that is used across different database management systems, including Oracle, MySQL, Microsoft SQL Server , and PostgreSQL , among others SQL has a rich set of syntax and functions that allow for powerful data manipulation and analysis capabilities

4. 4 SQL Server Architecture SQL Commands are processed by Query Processor

5. 5 History of SQL 1970 –E. Codd develops relational database concept 1974-1979 – System R with Sequel (later SQL) created at IBM Research Lab 1979 –Oracle markets first relational DB with SQL 1986 –ANSI SQL standard released 1989, 1992, 1999, 2003 –Major ANSI standard updates Current –SQL is supported by major database vendors American National Standards Institute  (ANSI)

6. 6 SQL Languages Data Definition Language (DDL) Commands that define a database, including creating, altering, and dropping tables and establishing constraints Data Manipulation Language (DML) Commands that maintain and query a database Data Control Language (DCL) Commands that control a database, including administrative privileges

7. 7 DDL, DML, DCL, and the database development process

8. DDL Data Definition Language

9. 9 SQL Database Definition Data Definition Language (DDL) Major CREATE statements: CREATE DATABASE –create database CREATE TABLE –defines a base table and its columns CREATE VIEW –defines a virtual table from one or more views

10. 10 CREATE DATABASE with default settings CREATE   DATABASE  PVFC; This SQL statement creates a new database named ‘ PFVC ' using the CREATE DATABASE statement in SQL Server. The new database will be created with default settings , such as the default file locations and file sizes , which can be configured based on the server's settings and configuration SQL Script download Link: https://wps.prenhall.com/bp_hoffer_edm_1/238/61126/15648380.cw/content/index.html

11. 11 CREATE DATABASE with custom settings /****** Object: Database [PFVC] ******/ CREATE DATABASE PVFC CONTAINMENT = NONE ON PRIMARY ( NAME = N'PVFC’ , -- N represents that string is on Unicode format FILENAME = N'F:\MSSQLExpress2022\MSSQL16.SQLEXPRESS\MSSQL\DATA\ PVFC.mdf ' , SIZE = 8192KB , MAXSIZE = UNLIMITED , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'PVFC_log ' , FILENAME = N'F:\MSSQLExpress2022\MSSQL16.SQLEXPRESS\MSSQL\DATA\ PVFC_log.ldf ' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB ) GO Log file records all transactions and changes made to the database. This log file allows DBAs to troubleshoot issues, recover lost data, and ensure data integrity

12. 12 ALTER DATABASE ALTER DATABASE [PVFC] MODIFY FILE ( NAME = N'PVFC' , MAXSIZE = 1024MB , FILEGROWTH = 1MB ); The script modifies the MAXSIZE to 1024 MB and FILEGROWTH to 1MB .

13. 13 DELETE DATABASE DROP DATABASE PVFC ; If your get an error on exec of the command, there can be several reasons which should be diagnosed: - Database is in use: Insufficient permissions: Database does not exist: Filesystem permission issues: Replication is enabled: Database is being restored:

14. 14 Table Creation General syntax for CREATE TABLE Steps in table creation: Identify data types for attributes Identify columns that can and cannot be null Identify columns that must be unique (candidate keys) Identify primary key – foreign key mates Determine default values Identify constraints on columns (domain specifications) Create the table and associated indexes

15. 15 Example SQL Script download Link: https://wps.prenhall.com/bp_hoffer_edm_1/238/61126/15648380.cw/content/index.html

16. 16 Example – SQL SERVER - Diagram

17. 17 database definition commands for Pine Valley Furniture Overall table definitions

18. End part 1

19. USE PVFC; GO CREATE TABLE [ Customer_T ] ( [ CustomerID ] [int] IDENTITY ( 1 , 1 ) NOT NULL, -- IDENTITY(seed, increment) [ CustomerName ] [ nvarchar ] ( 25 ) NOT NULL, [ CustomerAddress ] [ nvarchar ] ( 30 ) , [ CustomerCity ] [ nvarchar ] ( 20 ) , [ CustomerState ] [ nvarchar ] ( 2 ) , [ CustomerPostalCode ] [ nvarchar ] ( 9 ) , CONSTRAINT [ PK_Customer_T ] PRIMARY KEY CLUSTERED ( [ CustomerID ] )); GO 19 SQL Server Script for Creating Table CUSTOMER_T IDENTITY is commonly used with primary key column, when you want auto increment the row id. DBMS automatically insert next number when you add a new row. Primary keys can never have NULL values data in the table will be physically stored in the order of the clustered key

20. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [ dbo ] . [ Customer_T ] ( [ CustomerID ] [int] IDENTITY ( 1 , 1 ) NOT NULL, [ CustomerName ] [ nvarchar ] ( 25 ) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ CustomerAddress ] [ nvarchar ] ( 30 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ CustomerCity ] [ nvarchar ] ( 20 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ CustomerState ] [ nvarchar ] ( 2 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ CustomerPostalCode ] [ nvarchar ] ( 9 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [ PK_Customer_T ] PRIMARY KEY CLUSTERED ( [ CustomerID ] ASC ) WITH ( PAD_INDEX = OFF , IGNORE_DUP_KEY = OFF ) ON [PRIMARY] ) ON [PRIMARY] GO 20 SQL Server Script for Creating Table CUSTOMER_T – Version 2 Home Work See from the net about purpose of these terms

21. Creating Product_T Table SQL Server USE PVFC ; GO CREATE TABLE [ Product_T ] ( [ ProductID ] [int] NOT NULL, [ ProductDescription ] [ nvarchar ] ( 50 ) , ProductFinish VARCHAR ( 20 ) CHECK ( ProductFinish IN ( 'Cherry' , 'Natural Ash' , 'White Ash’ , 'Red Oak' , 'Natural Oak' , 'Walnut ’ ) ) , ProductStandardPrice NUMERIC ( 6 , 2 ), ProductLineID INT CONSTRAINT [ PK_Product_T ] PRIMARY KEY CLUSTERED ( [ ProductID ] ) ) ; GO This column has a CHECK constraint that restricts the allowed values to be one of the following: 'Cherry', 'Natural Ash', 'White Ash', 'Red Oak', 'Natural Oak', or 'Walnut CHAR(20) VS VCHAR (20) VS NVARCHAR (20)

22. Creating OrderLine_T Table SQL Server USE [PVFC] GO CREATE TABLE OrderLine_T ( OrderID NUMERIC ( 11 , ) NOT NULL, ProductID INT NOT NULL, OrderedQuantity NUMERIC ( 11 , ), CONSTRAINT OrderLine_PK PRIMARY KEY CLUSTERED ( OrderID , ProductID ), CONSTRAINT OrderLine_FK1 FOREIGN KEY ( OrderID ) REFERENCES Order_T ( OrderID ), CONSTRAINT OrderLine_FK2 FOREIGN KEY ( ProductID ) REFERENCES Product_T ( ProductID ) ); Composite Primary Key – composed of multiple attributes Non-nullable specifications Foreign Keys

23. Creating Order_T Table SQL Server USE [PVFC] GO CREATE TABLE Order_T ( OrderID NUMERIC ( 11 , ) NOT NULL, OrderDate DATE CONSTRAINT DF_Order_OrderDate DEFAULT GETDATE (), CustomerID NUMERIC ( 11 , ), CONSTRAINT Order_PK PRIMARY KEY CLUSTERED ( OrderID ), CONSTRAINT Order_FK FOREIGN KEY ( CustomerID ) REFERENCES Customer_T ( CustomerID ) ); Default Value (Current System Date)

24. 24 Changing and Removing Tables SQL SERVER ALTER TABLE statement allows you to change column specifications: ALTER TABLE CUSTOMER_T ADD [ cellno ] VARCHAR(2); ALTER TABLE [ Customer_T ] ADD CONSTRAINT [ df_value ] DEFAULT ( N’00000000' ) FOR [ cellno ]; ALTER TABLE CUSTOMER_T DROP COLUMN cellno ; DROP TABLE statement allows you to remove tables from your schema: DROP TABLE CUSTOMER_T; Set default value 8 0s string in Unicode for the column cellno

25. Duplicating a Table with/without Data SQL Server With All Data : SELECT * INTO temp_Customer_T -- new table FROM Customer_T ; --original table GO With No Data (Only Structure): SELECT * INTO temp2_Customer_T -- new table FROM Customer_T --original table WHERE 1 = ; --always false (condition for copying partial data can also be given) GO

26. Duplicating a Table in Temp Table with Data SQL Server SELECT * INTO # tempCustomer_T -- new local temp table FROM Customer_T ; -- original table GO -------------------------------------------------- SELECT * INTO ## tempCustomer_T -- new global temp table FROM Customer_T ; -- original table GO -------------------- A local temporary table (#TableName) is visible only to the SQL Server session that created it and is deleted when the session is closed. A global temporary table (##TableName) is visible to all SQL Server sessions and is deleted when the last session referencing the table is closed.

27. DML Data Manipulation Language

28. 28 Insert Statement Adds new row of data to a table: Command: To insert a row of data into a table where a value will be inserted for every attribute. Note Identity column will not be included because DBMS will insert next value of CustomerID automatically: - INSERT INTO [ Customer_T ] VALUES ( 'Contemporary Casuals' , '1355 S Hines Blvd' , 'Gainesville' , 'FL' , '326012871' );

29. 29 Insert Statement Command: To insert a row of data into a table where some attributes will be left null. Data values must be in the same order as the columns have been specified. Here, following statement was used to insert one row, because there was no ProductLineID for the end table INSERT INTO Product_T ( ProductID , ProductDescription , ProductFinish , ProductStandardPrice ) VALUES ( 1, ‘End Table’, ‘Cherry’, 175 );

30. END Part 2

31. 31 Insert Data from Another Table Command: Populating a table by using a subset of another table with the same structure for ProductFinish ‘Cherry’ only. INSERT INTO CherryProduct_T SELECT * FROM Product_T WHERE ProductFinish = ‘ Cherry ’ ;

32. 32 Batch Input The INSERT command is used to enter one row of data at a time or to add multiple rows as the result of a query Some versions of SQL have a special command or utility for entering multiple rows of data as a batch Oracle includes a program, SQL*Loader , which runs from the command line and can be used to load data from a file into the database. SQL Server includes a BULK INSERT command with Transact-SQL for importing data into a table or view. This can be especially useful for quickly importing large amounts of data.

33. 33 Bulk Insert - Example BULK INSERT DestinationTable FROM 'C:\path\to\datafile.csv ' WITH ( FIELDTERMINATOR = ',’, -- CSV field delimiter ROWTERMINATOR = '\n', -- CSV row delimiter ERRORFILE = 'C:\path\to\errorfile.log ', FIRSTROW = 2 -- if the first row is a header );

34. 34 Delete Statement Removes rows from a table Delete certain rows based on condition: DELETE FROM CUSTOMER_T WHERE STATE = 'HI' ; Delete all rows, when there is no condition: DELETE FROM CUSTOMER_T; --Dangerous Command

35. 35 Update Statement Modifies data in existing rows: For Product_ID Table, Change the value of Unit_Price Column value to 775 for Product_ID = 7 ; UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7 ;

36. 36 Merge Statement Merge data from temp2_... to temp_... MERGE INTO temp _Customer_T AS target USING temp2 _Customer_T AS source ON target . CustomerID = source . CustomerID WHEN MATCHED THEN UPDATE SET target . cellno = source . cellno WHEN NOT MATCHED THEN INSERT ( CustomerName , CustomerAddress , CustomerCity , CustomerState , CustomerPostalCode , cellno ) VALUES ( source . CustomerName , source . CustomerAddress , source . CustomerCity , source . CustomerState , s ource . CustomerPostalCode , source . cellno );

37. 37 Creating /Deleting Indexes Following statement creates Primary key. With CLSUTERED index by default : - ALTER TABLE temp_Customer_T ADD CONSTRAINT PK_CustomerID PRIMARY KEY ( CustomerID ); Syntax for Creating Indexes: ( Only one clustered index can be created on a table.) CREATE CLUSTERED INDEX idx_CustomerID ON temp_Customer_T ( CustomerID ); CREATE NONCLUSTERED INDEX idx_CustomerName ON temp_Customer_T ( CustomerName ); Syntax for Dropping Indexes: DROP INDEX temp_Customer_T . idx_CustomerID ; DROP INDEX temp_Customer_T . idx_CustomerName ;

38. 38 Clustered / Non-clustered Indexes A clustered index determines the physical order of the rows in a table. SQL Server rearranges the data in the table based on the values in the indexed column(s). A table can have only one clustered index, and the index key of the clustered index must be unique A nonclustered index is a separate structure from the table that contains the index key columns and a pointer to the location of the corresponding row in the table. A table can have multiple nonclustered indexes, and each nonclustered index can have its own set of columns.

39. PROCESSING SINGLE TABLES Select Statement

40. 40 SELECT Statement Used for queries on single or multiple tables Clauses of the SELECT statement: SELECT --mandatory clause List the columns (and expressions) that should be returned from the query FROM --mandatory clause Indicate the table(s) or view(s) from which data will be obtained WHERE Indicate the conditions under which a row will be included in the result GROUP BY Indicate categorization of results HAVING Indicate the conditions under which a category (group) will be included ORDER BY Sorts the result according to specified criteria

41. Select Statement Order of Processing 41 Figure shows the order in which SQL processes the clauses of a statement. Arrows indicate the paths that may be followed. Remember, only the SELECT and FROM clauses are mandatory. As each clause is processed, an intermediate results table is produced that will be used for the next clause Users do not see the intermediate results tables; they see only the final results. A query can be debugged by remembering the order shown in Figure

42. 42 SELECT Example Query : Which products have a standard price of less than $275? SELECT ProductDescription , ProductStandardPrice FROM Product_T WHERE ProductStandardPrice < 275 ;

43. 43 SELECT Example Using Alias Alias is an alternative column or table name Query : What is the address of the customer named Home Furnishings? Use an alias, Name , for the customer name . SELECT CUST . CustomerName AS Name , CUST . CustomerAddress FROM Customer_T AS Cust WHERE CUST . CustomerName = 'Home Furnishings' ;

44. 44 SELECT Example Using a Function You can create expressions, such as SUM or AVG, + , -, * or / , etc Query: What are the standard price and standard price if increased by 10 percent for every product? SELECT ProductID , ProductStandardPrice , ProductStandardPrice * 1.1 AS Plus10Percent FROM Product_T ;

45. 45 Using Function in SQL Standard SQL identifies a wide variety of mathematical, string and date manipulation, and other functions: - Mathematical : MIN, MAX, COUNT, SUM, ROUND, TRUNC, MOD String : LOWER , UPPER , CONCAT , SUBSTR Date : NEXT_DAY , ADD_MONTHS , MONTHS_BETWEEN Analytical : TOP

46. 46 AVG Function in SQL Query: What is the average standard price for all products in inventory? SELECT AVG ( ProductStandardPrice ) AS AveragePrice FROM Product_T ;

47. 47 COUNT Function in SQL Query: How many different items were ordered on order number 1004? SELECT COUNT (*) AS CountOrderLine FROM OrderLine_T WHERE OrderID = 1004 ; Here COUNT ( * ) counts the total number of rows. However, COUNT ( column_name ) returns the count of non null values in given column.

48. 48 Min Function Query: Alphabetically, what is the first product name in the Product table? SELECT MIN ( ProductDescription ) AS FirstProductDescription FROM Product_T ; It demonstrates that numbers are sorted before letters

49. 49 Using Wildcards Asterisk (*) as a wildcard is used in a SELECT statement. Wildcards may also be used in the WHERE clause when an exact match is not possible. LIKE ‘%Desk% ’ when searching ProductDescription will find all different types of desks carried by Pine Valley Furniture The underscore (_) is used as a wildcard character to represent exactly one character LIKE ‘_-drawer ’ when searching ProductName will find any products with specified drawers, such as 3-, 5-, or 8-drawer dressers.

50. 50 LIKE ‘ % Furni % ’ SELECT CUST . CustomerName AS Name , CUST . CustomerAddress FROM Customer_T AS Cust WHERE CUST . CustomerName LIKE '% Furni %’ ;

51. 51 LIKE ‘ _ urniture % ’ SELECT CUST . CustomerName AS Name , CUST . CustomerAddress FROM Customer_T AS Cust WHERE CUST . CustomerName LIKE ‘‘_ urniture %’ ;

52. 52 Comparison Operators Query: Which orders have been placed since 24-OCT-2010 ? SELECT OrderID , OrderDate FROM Order_T WHERE OrderDate > '24-OCT-2010' ;

53. End Week 9