SQL: Importing Data and Inserting Table Rows

Importing Data

• Several techniques exist for loading initial data into a database
• Custom built software application
• Import data wizards in RDBMS applications
• SQL Server function BULK INSERT
• Command line tool bcp Utility
• OPENROWSET function
• SSIS (SQL Server Integration Services) for complex data loads
• The Import data wizard in SSMS can import flat files like .txt and .csv and also data from external sources

Inserting Table Rows

• When inserting values into all columns of a table, specifying the column names is optional
• Example of inserting a single row into the PRODUCT table:

INSERT INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_QTY, P_MIN, P_PRICE,
P_DISCOUNT, V_CODE)
VALUES ('11QER/31','Power painter, 15 psi., 3-nozzle','2018-11-07', 8,
5,109.99,0.00,25595)

• Example of inserting multiple rows into the PRODUCT table:

INSERT INTO PRODUCT (P_CODE, P_DESCRIPT, P_INDATE, P_QTY, P_MIN, P_PRICE, P_DISCOUNT,
V_CODE)
VALUES
('11QER/31','Power painter, 15 psi., 3-nozzle','2018-11-07', 8,
5,109.99,0.00,25595),
('13-Q2/P2','7.25-cm. pwr. saw blade','2018-12-14', 32, 15, 14.99,0.05,21344)

Inserting Table Rows with a Subquery

• A subquery, also called a nested or inner query, is a query within another query
• The inner query executes first
• When inserting rows, the subquery's values must match the attributes and data types of the table
• Syntax for inserting rows into an existing table using a subquery: INSERT INTO tablename SELECT columnlist FROM tablename
• Syntax for creating a new table and inserting rows from an existing table using a subquery: SELECT columnlist INTO newtablename FROM oldtablename
• Example: SELECT * INTO PART FROM PRODUCT
• No primary or foreign key rules are applied to the new table; recreate referential integrity using ALTER TABLE commands

Listing Data

• To select all fields and all rows from a table, use the following SQL: SELECT * FROM PRODUCT
• To select partial table content by placing restrictions on rows:

SELECT *
FROM PRODUCT
WHERE V_CODE = 21344

• To select partial field content by specifying fields: SELECT P_CODE, P_DESCRIPT, P_PRICE, P_DISCOUNT FROM PRODUCT
• To use comparison operators to list a subset of rows:

SELECT P_CODE, P_DESCRIPT, P_PRICE, P_DISCOUNT, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344

• To use comparison operators to restrict rows and alias a column:

SELECT P_CODE, P_DESCRIPT, P_PRICE, P_DISCOUNT, V_CODE AS VENDOR_CODE
FROM PRODUCT
WHERE P_PRICE > 50.00

Advanced SELECT Queries

• ORDER BY clause sorts results in ascending order by default, based on the specified column
• Ordering by multiple columns is known as a cascading order sequence
• Example ORDER BY clause:

SELECT *
FROM VENDOR
ORDER BY V_NAME ASC, V_CONTACT DESC, V_PHONE

• DISTINCT produces a list of unique values
• Example DISTINCT clause: SELECT DISTINCT V_CODE FROM PRODUCT
• COUNT is an aggregate function used to tally non-null attribute values
• COUNT can be used with DISTINCT
• Example COUNT clauses:

SELECT COUNT(V_CODE) AS V_CODE_COUNT FROM PRODUCT
SELECT COUNT(DISTINCT V_CODE) AS V_CODE_COUNT FROM PRODUCT

MIN and MAX Functions

• MIN finds the lowest value in a column
• MAX finds the highest value in a column
• Example:

SELECT MIN(V_NAME) FROM VENDOR
SELECT MAX(V_CODE) FROM PRODUCT

SUM Function

• SUM returns the sum of a set of numeric records
• Example:

SELECT SUM(P_PRICE) FROM PRODUCT
SELECT SUM(P_QTY * P_PRICE) AS TOTALVALUE FROM PRODUCT

AVG Function

• AVG returns the average of a set of numeric records
• Example:

SELECT P_CODE, P_DESCRIPT, P_QTY, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)
ORDER BY P_PRICE DESC

Grouping Data

• The GROUP BY clause summarizes rows with the same values
• SQL syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s)

• The SELECT column list must include a combination of column names and aggregate functions
• The GROUP BY clause's column list requires all non-aggregate function columns named in the SELECT column list
• Grouping can occur on any aggregate function columns from the SELECT column list
• The GROUP BY clause column list may include any columns from tables in the FROM clause of the SELECT statement, even if they are not specified in the SELECT column list
• The GROUP BY clause is valid only when used in conjunction with an aggregate function

Filtering Data with the HAVING Clause

• The HAVING clause extends the GROUP BY clause
• It works like WHERE in the SELECT statement but applies to the output of the GROUP BY operation rather than individual rows
• SQL syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Updating Data with Transactions

• Verifying updates and deletions is possible using transactions before changes are committed to the database
• A transaction represents one or more commands executed as a single unit
• Changes are committed with COMMIT TRANSACTIONor discarded with ROLLBACK TRANSACTION

BEGIN TRANSACTION
- -Updating a single cell
UPDATE PRODUCT
SET P_INDATE = '2024-02-28'
WHERE P_CODE = 'WR3/TT3'

SELECT * from PRODUCT WHERE P_CODE = 'WR3/TT3'
COMMIT TRANSACTION

• COMMIT and ROLLBACK work only with data manipulation commands that add, modify, or delete table rows

Deleting Data

• SQL code example

BEGIN TRANSACTION

SELECT * from PRODUCT WHERE P_CODE = 'WR3/TT3'
DELETE FROM PRODUCT WHERE P_CODE = 'WR3/TT3'
SELECT * from PRODUCT WHERE P_CODE = 'WR3/TT3'

ROLLBACK TRANSACTION

• DELETE is set-oriented
• The WHERE condition is optional; if unspecified, all rows will be deleted from the specified table

Joining Tables

• Self Joins: Tables can join to themselves
• Specify an ALIAS for a self join
• Queries can include columns, computed columns, aliases, and aggregate functions

Outer Joins

• The specifier OUTER is not needed
• LEFT joins typically offer better readability
• Right Outer Join: Returns all records from the table specified as the JOIN table
  • If the table in the FROM clause lacks a foreign key record, NULL values will be provided for those columns
• Left Outer Join: Returns all records from the table in the FROM clause
  • If the table being joined lacks a foreign key record, NULL values will be provided for those columns

UNION, UNION ALL, INTERSECT

• UNION combines rows from multiple queries but excludes duplicate rows
  • Syntax: query UNION query
• UNION ALL combines rows from multiple queries and includes duplicate rows
  • Syntax: query UNION ALL query
• INTERSECT identifies rows duplicated across tables based on values unrelated to the relationship
  • Syntax: query INTERSECT query

Subqueries

• A subquery is a query nested within a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery
• They perform operations in steps
• Subqueries get evaluated first
• Comparison operators like ALL and ANY can be compared

SELECT P_CODE, P_QTY * P_PRICE
FROM PRODUCT
WHERE P_QTY * P_PRICE > ALL (SELECT P_QTY * P_PRICE
FROM PRODUCT
WHERE V_CODE IN (SELECT V_CODE
FROM VENDOR
WHERE C_COUNTRY_ID = 206))

ALL and ANY Operators

• Used in subqueries
• Allow comparisons of a single column against a range of other values
• ANY returns TRUE if any subquery values meet the specified conditions
• ALL returns TRUE if all subquery values meet the specified conditions
• Example that returns 1 row because ANY requires at least one value in the result of the LINE table to match the WHERE condition in the PRODUCT table

SELECT P_CODE, P_DESCRIPT, P_QTY
FROM PRODUCT
WHERE P_QTY = ANY (SELECT LINE_UNITS
FROM LINE
WHERE LINE_UNITS > 4);

• Example that returns 0 rows because requires that all the values need to match to return a result

SELECT P_CODE, P_DESCRIPT, P_QTY
FROM PRODUCT
WHERE P_QTY = ALL (SELECT LINE_UNITS
FROM LINE
WHERE LINE_UNITS > 4)

Subqueries in the FROM Clause

• A subquery can be nested inside the FROM clause
• Example code:

SELECT DISTINCT C.CUS_CODE, C.CUS_LNAME
FROM CUSTOMER C
INNER JOIN (SELECT DISTINCT I.CUS_CODE
FROM INVOICE I
INNER JOIN LINE L ON I.INV_NUMBER = L.INV_NUMBER
WHERE L.P_CODE = '13-Q2/P2') I1 ON C.CUS_CODE = I1.CUS_CODE
INNER JOIN (SELECT DISTINCT I.CUS_CODE
FROM INVOICE I
INNER JOIN LINE L ON I.INV_NUMBER = L.INV_NUMBER
WHERE L.P_CODE = '23109-HB') I2 ON I1.CUS_CODE = I2.CUS_CODE

• The above query finds customers who purchased both products. I2 virtual table returns all customers who purchased product '23109-HB'
• I1 virtual table returns all customers who purchased product '13-Q2/P2'
• The INNER JOIN between tables I1 and I2 returns a single row – CUS_CODE: 10014 – because the CUS_CODE is returned in both results

Correlated Subqueries

• Executes serially, not independently, running once for each outer query row
• Mostly used in the WHERE clause

SELECT V_CODE, V_NAME
FROM VENDOR V
WHERE EXISTS (SELECT * FROM PRODUCT P
WHERE P.P_QTY < P.P_MIN * 2
AND V.V_CODE = P.V_CODE)

• Above checks for each vendor code in the vendor table if the quantity of products are double the minimum required

SELECT INV_NUMBER, P_CODE, LINE_UNITS
FROM LINE LS
WHERE LS.LINE_UNITS > (SELECT AVG(LINE_UNITS)
FROM LINE LA
WHERE LA.P_CODE = LS.P_CODE)

• Inner query (table LA) computes the average units sold of the product that matches the P_CODE of the outer query (table LP)

Views

• A virtual table based on a select query
• Queries can contain columns, computed columns, aliases, and aggregate functions
• Views are dynamically updated
• Views offer security by restricting specific data or users
• Can be used as a basis for reports
• ORDER BY clause is not valid in views
• Syntax: CREATE VIEW VW_PRODUCT_REORDER AS (SELECT query)

SQL Functions

• SQL functions are pre-built actions that perform calculations, data manipulation, and result returns

String Functions

• Used to manipulate string data types, examples being: CONCAT, SUBSTRING, TRIM, LEN

Numeric Functions

• Different from aggregate functions
• Numeric functions are algebraic, trigonometric, and algorithmic, examples being: ABS, ROUND, TRUNC

Date & Time Functions

• All date functions take one parameter (of a date or character type) and return a value (character, numeric or date type), examples being: YEAR, MONTH, DAY, DATE()

Conversion Functions

• Transform the value of a given data type and converts it to the equivalent value in another data type ex: FLOAT -> INT, FLOAT -> VARCHAR, MONEY -> VARCHAR, DATE/TIME -> VARCHAR

User-Defined Functions

• User-defined functions supplement built-in functions
• Users create them for a specific need
• These functions can accept parameters, perform a calculation, and return a value
• They reduce network traffic by storing on the server, increasing performance by executing a batch rather than individual commands
• Reduces code duplication through code isolation and sharing, minimizing errors and costs
• Syntax:

CREATE FUNCTION function_name (parameters)
RETURNS data_type AS
BEGIN
SQL statements ...
RETURN value
END

Stored Procedures

• Collection of SQL statements saved in the database for reuse
• Encapsulate business logic
• They reduce network traffic by storing on the server, increasing performance by executing a batch rather than individual commands
• Reduces code duplication through code isolation and maintenance
• Syntax:

CREATE PROCEDURE procedure_name [(argument[IN/OUT] data_type, ...)]
[AS]
[variable_name data_type[: = initial_value] ]

SQL statements
....
[GO]

Cursors

• A construct in procedural SQL to hold data rows returned by a query
• An implicit cursor automatically created in procedural SQL when the SQL statement returns one row
• An explicit cursor is created to hold the output of a SQL statement that may return 0 to n rows
• Useful for processing rows one at a time; other processes should be investigated as cursors slow down performance
• Syntax:

DECLARE cursor_name CURSOR FOR
select-statement
OPEN cursor_name
FETCH NEXT FROM cursor_name INTO variable_list

WHILE @@FETCH_STATUS = 0
BEGIN
perform operations as required...
END
CLOSE cursor_name
DEALLOCATE cursor_name