Basic CRUD in SQL Server, Exams of Fashion Business Basics

Download Basic CRUD in SQL Server and more Exams Fashion Business Basics in PDF only on Docsity!

Basic CRUD in SQL Server

Create, Retrieve, Update, Delete

using SQL queries

Table of Contents

1. Query Basics

2. Retrieving Data

3. Writing Data

4. Modifying Existing Records

▪ Structured Query Language

▪ Declarative language

▪ Close to regular English

▪ Supports definition, manipulation and access control of records

▪ Transact-SQL (T-SQL) – SQL Server's version of SQL

▪ Supports control flow (if statements, loops)

▪ Designed for writing logic inside the database

What are SQL and T-SQL?

SELECT FirstName, LastName, JobTitle FROM Employees

SQL – Examples

SELECT FirstName, LastName, JobTitle FROM Employees INSERT INTO Projects(Name, StartDate) VALUES('Introduction to SQL Course', '1/1/2006') SELECT * FROM Projects WHERE StartDate = '1/1/2006' UPDATE Projects SET EndDate = '8/31/2006' WHERE StartDate = '1/1/2006' DELETE FROM Projects WHERE StartDate = '1/1/2006'

Retrieving Data

Using SQL SELECT

Capabilities of SQL SELECT

Selection

Take a subset of the rows

Projection

Take a subset of the columns

Table 1 (^) Table 2 Join

Combine tables by

some column

▪ Aliases rename a table or a column heading ▪ You can shorten fields or clarify abbreviations

Column Aliases

SELECT EmployeeID AS ID, FirstName, LastName FROM Employees ID FirstName LastName 1 Guy Gilbert 2 Kevin Brown … … … SELECT c.Duration, c.ACG AS 'Access Control Gateway' FROM Calls AS c Display Name

▪ You can concatenate column names using the + operator ▪ String literals are enclosed in single quotes ▪ Column names containing special symbols use brackets

Concatenation Operator

SELECT FirstName + ' ' + LastName AS [Full Name], EmployeeID AS [No.] FROM Employees Full Name No. Guy Gilbert 1 Kevin Brown 2 … …

Solution: Employee Summary

SELECT FirstName + ' ' + LastName AS [Full Name], JobTitle, Salary FROM Employees Concatenation Column Alias

Filtering the Selected Rows

▪ Use DISTINCT to eliminate duplicate results

▪ Filter rows by specific conditions using the WHERE clause

▪ Other logical operators can be used for greater control

SELECT LastName, DepartmentID

FROM Employees

WHERE DepartmentID = 1

SELECT LastName, Salary FROM Employees

WHERE Salary <= 20000

SELECT DISTINCT DepartmentID

FROM Employees

▪ NULL is a special value that means missing value ▪ Not the same as 0 or a blank space ▪ Checking for NULL values

Comparing with NULL

SELECT LastName, ManagerId FROM Employees WHERE ManagerId IS NULL SELECT LastName, ManagerId FROM Employees WHERE ManagerId IS NOT NULL SELECT LastName, ManagerId FROM Employees WHERE ManagerId = NULL This is always false!

Sorting Result Sets

▪ Sort rows with the ORDER BY clause

▪ ASC: ascending order, default ▪ DESC: descending order SELECT LastName, HireDate FROM Employees ORDER BY HireDate LastName HireDate Gilbert 1998 - 07 - 31 Brown 1999 - 02 - 26 Tamburello 1999 - 12 - 12 … … SELECT LastName, HireDate FROM Employees ORDER BY HireDate DESC LastName HireDate Valdez 2005 - 07 - 01 Tsoflias 2005 - 07 - 01 Abbas 2005 - 04 - 15 … …

▪ Create a view that selects all information about the highest peak

▪ Name the view v_HighestPeak

▪ Note: Query Geography database

Problem: Highest Peak

SELECT * FROM v_HighestPeak

▪ TOP(x) selects the first x values

Solution: Highest Peak

CREATE VIEW v_HighestPeak AS SELECT TOP (1) * FROM Peaks ORDER BY Elevation DESC Greatest value first Sorting column