Skip to content
Author: Tianle Yuan

** Higher Level Management**⚓︎

Glance of contents⚓︎

Here are some higher-level uses:

  • DROP - Delete database or table.
  • Stored PROCEDURES - Store SQL code for reuse.
  • SELECT DISTINCT - extracts distinct data from a database
  • LIKE - Specify the data format in WHERE.
  • WILDCARDS - For regex expression.
  • [ALTER TABLE]
  • [SELECT INTO]
  • [INSERT INTO SELECT]
  • [Case]

DROP⚓︎

DROP

The DROP statement is used to delete the whole database or table.

Syntax
SQL
DROP DATABASE databasename;
DROP TABLE table_name;
TRUNCATE TABLE table_name;  -- delete all the data inside a table, but not the table itself.
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123,'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (123,'Salem'); INSERT INTO Marks_family (PersonID, City) VALUES (223,'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (123,'Albany'); DROP TABLE Marks_family;

Stored PROCEDURES⚓︎

Stored PROCEDURES

A stored PROCEDURES is a prepared SQL code that you can save, so the code can be reused over and over again.

Syntax
SQL
-- Stored Procedure Syntax
CREATE PROCEDURE procedure_name --definition
AS
sql_statement                   --function body
GO;
-- Execute a Stored Procedure
EXEC procedure_name;            --use the function
click to see an example

-- EG1 -- Stored Procedure Syntax CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode GO; -- Execute a Stored Procedure EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

-- EG2 -- Stored Procedure Syntax CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO; -- Execute a Stored Procedure EXEC SelectAllCustomers;

SELECT DISTINCT⚓︎

SELECT DISTINCT

The SELECT DISTINCT statement is used to return only distinct (different) values.

Syntax
SQL
SELECT DISTINCT column1, column2, ...
FROM table_name;
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, City) VALUES (123,'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (123,'Salem'); INSERT INTO Marks_family (PersonID, City) VALUES (223,'Albany'); INSERT INTO Marks_family (PersonID, City) VALUES (123,'Albany'); SELECT * FROM Marks_family;
SELECT DISTINCT PersonID, City FROM Marks_family; --will return distinct tuples SELECT DISTINCT City FROM Marks_family; SELECT DISTINCT PersonID FROM Marks_family;

LIKE⚓︎

LIKE

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Syntax
SQL
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
LIKE Operator Description
WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
click to see an example

SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

Wildcard⚓︎

Wildcard

Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

click to see an example
Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat
- Represents any single character within the specified range c[a-b]t finds cat and cbt

References:⚓︎