SQL Workshop Flashcards
Create a table with
1) Auto increment
2) uinique primary key
3) char , int and date-time columns .
CREATE TABLE Worker ( WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, FIRST_NAME CHAR(25), LAST_NAME CHAR(25), SALARY INT(15), JOINING_DATE DATETIME, DEPARTMENT CHAR(25) );
Insert values into table the columns Column 1 and Column 2
INSERT INTO Table
(Column1, Column2) VALUES (001, ‘Monika’)
Get all unique elements from duplicates in certain column of sql table
SELECT DISTINCT column1, column2, …
FROM table_name;
Get all elements form a certain column in SQL table
Select column_name from table_name
Get entries from certain column while specifying criteria
SELECT column1, column2, …
FROM table_name
WHERE column1>10;
Get elements that DO NOT satisfy specific condition in said column
SELECT column1, column2, …
FROM table_name
WHERE NOT colum1=”Sunny”;
Get elements with multiple filters applied at the same time
SELECT * FROM Customers
WHERE Country=’Germany’ AND City=’Berlin’;
Get elments that is union of completely different search filters
SELECT * FROM Customers
WHERE Country=’Germany’ OR Country=’Spain’;
Get elements in Ascending order
SELECT * FROM Table
ORDER BY column ;
Get elements in Descedning order
SELECT * FROM Table
ORDER BY column DESC;
Update specific elements based on filter conditions
UPDATE Customers
SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;
What happens if I do this?
UPDATE Customers
SET ContactName=’Juan’;
All entries will get updated regradless of any country , age , City etc. It is essential that while updating the correct filters are applied .Or it will corrupt entire data.
How to delete specific elements in table?
DELETE FROM table_name WHERE Name = “Value”;
What is one important warning to remember while deleting entries in SQL ?
We should careful regarding the condition we set in DELETE query
DELETE FROM table_name WHERE condition;
If we are not mindful of the condition then we might end up deleting the wrong entries.
How to delete all records from a certain table?
DELETE FROM table_name;
What happens if we execute the follwing line?
DELETE Age from Table where Name=’Sunny’;
It will raise me a syntax error .Because we cant delete a specific column from table . Deletion removes the complete entry ,not just a column.
How to update all entries in Table?
UPDATE Customers
SET ContactName = ‘Alfred Schmidt’;
What happens if I exectue the following line?
Update Student_Table where Name= “Sunny”
It will raise me an error because we havent specified the “set” component in update.
What are important components of update statement?
Udate table , SET values and condition
What are important components of delete statement?
delete table and condition
What happens if I execute the following line?
Update Student_Table set Age = 20 where “Name” = Sunny
It will raise me an error because the field should not be a string and the value its checking (Sunny) should be a string.
How to get top 10 elements of a table
SELECT S.No,Name
FROM Student
LIMIT 10;
How to get bottom 10 element
SELECT S.No,Name
FROM Student ORDER by S.No DESC
LIMIT 10;
What type of statistical attributes can we calculate from numerical fields in data table?
We can calclulate , COUNT , MIN , MAX, SUM AND AVG
Format to calculate the statistical attributes of a table
Select MIN(Age) from Students where City = "Frankfurt" We can use the same for COUNT , MAX, SUM or AVG
Get Elements where the name starts with letter ‘a’
SELECT * FROM Customers
WHERE CustomerName LIKE ‘a%’;
What key word should i use in case I want to find words that follow a certain pattern?
We should use the key words “LIKE”
Get elements that end with letter ‘a’
SELECT * FROM Customers
WHERE CustomerName LIKE ‘%a’;
What happens if I use the following syntax?
SELECT * FROM Customers
WHERE CustomerName LIKE ‘*a’;
This will raise me and error if I am using mySQL . But that wont be the case if we are using MS Access
Get elements where the middle word is “or”
SELECT * FROM Customers
WHERE CustomerName LIKE ‘%or%’;
Get element that has second word ‘r’
SELECT * FROM Customers
WHERE CustomerName LIKE ‘_r%’;
Get elements that has starting letter ‘a’ and has atleast 3 words in the string
SELECT * FROM Customers
WHERE CustomerName LIKE ‘a__%’;
Note: There are 2 ‘_’ in the syntax above
Get elements that start with ‘a’ and end with ‘o’
SELECT * FROM Customers
WHERE ContactName LIKE ‘a%o’;
Get Customers with a CustomerName that does NOT start with “a”
SELECT * FROM Customers
WHERE CustomerName NOT LIKE ‘a%’;
Get all customers with a City starting with “b”, “s”, or “p”
SELECT * FROM Customers
WHERE City LIKE ‘[bsp]%’;
Get all customers with a City starting with “a”, “b”, “c”,”d”….till “p”
SELECT * FROM Customers
WHERE City LIKE ‘[a-p]%’;
Get all customers with a City NOT starting with “b”, “s”, or “p”
SELECT * FROM Customers
WHERE City LIKE ‘[!bsp]%’;
What happens if I exectue the following syntax?
SELECT * FROM Customers
WHERE City LIKE ‘![bsp]%’;
It wont get the desired results , It wont give the names that DONT start with bsp . Please check this in terminal and then update this card
Get all customers that are located in “Germany”, “France” or “UK”
SELECT * FROM Customers
WHERE Country =”Germany” OR Country =”France” OR Country =”UK”
What is an alternative for getting customers from “Germany”, “France” or “UK”
SELECT * FROM Customers
WHERE Country IN (‘Germany’, ‘France’, ‘UK’);
Get all customers that are NOT located in “Germany”, “France” or “UK”
SELECT * FROM Customers
WHERE Country NOT IN (‘Germany’, ‘France’, ‘UK’);
Alternative for getting customers that are not located in “Germany”, “France” or “UK”
SELECT * FROM Customers
WHERE NOT Country =”Germany” OR NOT Country =”France” OR NOT Country =”UK”
Why do we use IN functionality when we could use OR logic or other logical expressions to get our results
Because we cant always cover ALL checking conditions . Sometimes there might be too many to cover .”IN” can be useful in situations like these.
Get all customers that are from the same countries as the suppliers
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
Note : If we used OR logical expressions in statements like these it would take forever.We might as well search manually
Get all products with a price between 10 and 20
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
Get all products outside a specific range like say less than 10 or greater than 20
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
Get all products with a price between 10 and 20. AND In addition; do not show products with a CategoryID of 1,2, or 3:
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);
What happens if I execute the following syntax?
SELECT * FROM Customers
WHERE Country NOT IN [‘Germany’, ‘France’, ‘UK’];
It raises me an error , Square brackets are not allowed .It should be (‘Germany’, ‘France’, ‘UK’);
What happens if I try to arrange names in ascending order
example : Select name from student order by name DESC;
It will arrange in all names in opposite chronological order ,
Example : Z to A
Manjimup , Lousiana , Herbert etc
Get all products with a ProductName BETWEEN Carnarvon Tigers and Chef Anton’s Cajun Seasoning
SELECT * FROM Products
WHERE ProductName BETWEEN “Carnarvon Tigers” AND “Chef Anton’s Cajun Seasoning”;
Get all products with a ProductName BETWEEN Carnarvon Tigers and Chef Anton’s Cajun Seasoning WHILE maintaining chronological order
SELECT * FROM Products
WHERE ProductName BETWEEN “Carnarvon Tigers” AND “Chef Anton’s Cajun Seasoning” ORDER BY ProductName;
Get all products with a ProductName NOT BETWEEN Carnarvon Tigers and Mozzarella di Giovanni
SELECT * FROM Products
WHERE ProductName NOT BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’;
