Show Menu
Cheatography

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
*Renam­ing­&
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;
 

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,
'a%' Finds any values that start with "­a".
'%or%' Finds any values that have "­or" in any position.
... FROM table_name WHERE columnN LIKE pattern;
'_r%' Finds any values that have "­r" in the second position 'a%o'
Finds any values that start with "­a" and ends with "­o"
SELECT * FROM Customers WHERE Country IN ('Germ­any', 'France', 'UK');
SELECT * FROM Customers WHERE Country NOT IN ('Germ­any', 'France', 'UK');
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
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 you’ll need
 
DECLARE @produ­ct_name VARCHA­R(MAX), @list_­price DECIMAL;
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;
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­_na­me;....FETCH NEXT FROM cursor INTO variab­le_­list;
OPEN cursor­_pr­oduct; FETCH NEXT FROM cursor­_pr­oduct INTO @produ­ct_­name, @list_­price;
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;
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­_name; DEALLOCATE cursor­_name;
CLOSE cursor­_pr­oduct; DEALLOCATE cursor­_pr­oduct;
                   
 

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

          SQL Server Cheat Sheet
          Essential MySQL Cheat Sheet
          Oracle SQL Developer Keyboard Shortcuts

          More Cheat Sheets by datamansam

          Reg Ex CheatSheet Cheat Sheet
          Jupyter Notebook Keyboard Shortcuts
          Numpy Crib Cheat Sheet