Show Menu
Cheatography
  1. Home >
  2. Programming >

DataBase Concepts Cheat Sheet by

Overview of SQL keywords

DDL

TABLE
COLUMN
Creation
CREATE TABLE table_name
ALTER TABLE table_name
column­_name1, data_t­ype­(size), column­_name2 data_t­ype­(size), column­_name3 data_t­ype­(size),
ADD column­_name datatype
Renaming
RENAME old_ta­ble­_name
ALTER TABLE table_name
TO new_ta­ble­_name;
RENAME old_co­lum­n_name TO new__c­olu­mn_­name;
Deleting
DROP TABLE Table_Name
ALTER TABLE Table_Name DROP COLUMN column­_name;

DML

ADDING ROWS
DELETING ROWS
INSERT INTO table_name (column1, column2, column3, ...)
DELETE FROM table_name
VALUES (value1, value2, value3, ...);
WHERE condition (eg , column2 = value2,;
UPDATING ROWS
UPDATE table_name SET column1 = value1
, column2 = value2, ... WHERE condition;

DQL

Wildcard Pattern Matching
SELECT column1,
column2,
FROM table_name
WHERE column2 LIKE pattern;
'_r%' Finds any values that have "­r" in the second position
'a%' Finds any values that start with "­a".
 
'a%o' Finds any values that start with "­a" and ends with "­o"
'%or%' Finds any values that have "­or" in any position.
Sorting
Sorts the results of a column alphab­eti­cally or numeri­cally, ascending by default
  
SELECT name_o­f_c­olumn1, name_o­f_c­olumn2 FROM name_o­f_table WHERE condition1 OR condit­ion2... ASC/DESC;
Contains
SELECT * FROM TableName
WHERE Country IN ('val1', 'val2', 'val3');
CURSOR
A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query
DECLARE all variables you’ll need
DECLARE … CURSOR FOR SELECT query, where you’ll declare a cursor and also define the query related to (popul­ating) that cursor
DECLARE cursor­_name CURSOR FOR select­_st­ate­ment;
OPEN the cursor and FETCH NEXT from the cursor
OPEN cursor­_na­me;....FETCH NEXT FROM cursor INTO variab­le_­list;
In the WHILE loop you’ll test the @@FETC­H_S­TATUS variable (WHILE @@FETC­H_S­TATUS = 0). If the condition holds, you’ll enter the loop BEGIN … END block and perform statements inside that block
WHILE @@FETC­H_S­TATUS = 0 BEGIN FETCH NEXT FROM cursor­_name; END;
CLOSE the cursor and DEALLOCATE it.
CLOSE cursor­_name; DEALLOCATE cursor­_name;
CASE
Goes through conditions and returns the value corres­ponding to the first true condition (like an if-the­n-else statement)
  
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE result END;
 

DDL Examples

TABLE
COLUMN
Creation
CREATE TABLE friends
ALTER TABLE Friends D
name varcha­r(100),
ADD id int;
age int );
Renaming
RENAME friends
ALTER TABLE fam
TO fam;
RENAME name TO first_­name;
Deleting
DROP TABLE Fam;
ALTER TABLE Fam DROP COLUMN age;

DML Example

ADDING ROWS
DELETING ROWS
INSERT INTO fam (id, name, age)
DELETE FROM fam
VALUES (1, 'Ross', 31);
WHERE condition (id = 1);
UPDATING ROWS
UPDATE fam SET name = 'Rachel Geller'
WHERE id = 2;

DQL example

Wildcard Pattern Matching
SELECT column1,
column2,
FROM table_name
WHERE column2 LIKE pattern;
'_r%' Finds any values that have "­r" in the second position
'a%' Finds any values that start with "­a".
 
'a%o' Finds any values that start with "­a" and ends with "­o"
'%or%' Finds any values that have "­or" in any position.
Order By
   
SELECT name_o­f_c­olumn1, name_o­f_c­olumn2 FROM name_o­f_table WHERE condition1 OR condit­ion2... ASC/DESC;
Contains
SELECT * FROM Customers WHERE Country IN ('Germ­any', 'France', 'UK');
  
SELECT * FROM Customers WHERE Country NOT IN ('Germ­any', 'France', 'UK');
CURSOR
Allows us to update one row at a time or perform an admini­str­ative process such as SQL Server database backups in a sequential manner.
DECLARE all variables we need
  
DECLARE @produ­ct_name VARCHA­R(MAX), @list_­price DECIMAL;
DECLARE … CURSOR FOR SELECT naming our cursor and the query to find the values it will contain
  
DECLARE cursor­_pr­oduct CURSOR FOR SELECT produc­t_name, list_price FROM produc­tio­n.p­rod­ucts;
OPEN the cursor and FETCH NEXT from the cursor
  
OPEN cursor­_pr­oduct; FETCH NEXT FROM cursor­_pr­oduct INTO @produ­ct_­name, @list_­price;
The WHILE loop to test the weather our condition returned values, when 0 (meaning rows were returned), we fetch the specified values
  
WHILE @@FETC­H_S­TATUS = 0 BEGIN PRINT @produ­ct_name + CAST(@­lis­t_price AS varchar); FETCH NEXT FROM cursor­_pr­oduct INTO @produ­ct_­name, @list_­price; END;
CLOSE the cursor and DEALLOCATE it.
  
CLOSE cursor­_pr­oduct; DEALLOCATE cursor­_pr­oduct;
Case
SELECT OrderID, Quantity, CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30' WHEN Quantity = 30 THEN 'The quantity is 30' ELSE 'The quantity is under 30' END AS Quanti­tyText FROM OrderD­etails;
  
SELECT Custom­erName, City, Country FROM Customers ORDER BY (CASE WHEN City IS NULL THEN Country ELSE City END);
                   
 

Metadata

  • Languages:
  • Published: 19th November, 2021
  • Last Updated: 28th March, 2022
  • Rated: 0 out of 5 stars based on 1 ratings

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          DataBase modelling Cheat Sheet
          SQL Cheat Sheet

          More Cheat Sheets by datamansam