SQL Workshop COPY Flashcards
What happens if execute the following code?
Create newTable(col1 INT, col2 CHAR(25));
It will raise me error because you forgot to mention the keyword table .
Correct Syntax
Create table newTable(col1 INT, col2 CHAR(25);
What is the difference between unqiue Key and primary key?
There may be many unique key columns in a table but there can be only ONE primary key column.
What happens when I assign a certain column as Unique key?
It makes sure that all the entries in that column are unique and has no duplicates
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) );
What happens if I try to execute the following code?
CREATE TABLE NewTable(S.No INT PRIMARY KEY);
It will raise and error because ‘.’ is not allowed for COLUMN name assingment. ‘_’ Is allowed.So the appropriate word would be ‘S_No’
Insert values into table that has columns ‘Column1’(INT) and ‘Column2’(string)
INSERT INTO Table_Name (Column1, Column2) VALUES (001, ‘Monika’)
What happens if I execute this ?
INSERT INTO TABLE newtable3(col1,col2,col3) VALUES (1,2,’Hello’);
This will raise me an error because we DONT HAVE to specify keyword table in this case .
Correct Syntax:
INSERT INTO newtable3(col1,col2,col3) VALUES (1,2,’Hello’);
What is the differnce between create and Insert in terms of syntax?
In case of Create we will have to mention the keyword TABLE
But in case of Insert we SHOULDNT mention the keyword , or else it will give me syntax error
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 elements from certain column while specifying criteria
SELECT column1, column2, … FROM table_name WHERE column1>thresh_val;
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 condtions are 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 Customers ORDER BY Country ASC;
Get elements in Descedning order
SELECT * FROM Customers ORDER BY Country DESC;
Update specific records of a table based on search 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.
What happens if we execute the following code?
UPDATE Customers SET ContactName = ‘Amos’ AND Country = ‘India’ WHERE CustomerID = 1;
I will raise me an error , because for set compnonet we shouldnt use AND, instead we should use comma
UPDATE Customers SET ContactName = ‘Amos’,Country=’India’ WHERE CustomerID = 1;
How to delete specific elements in table?
DELETE FROM table_name WHERE Name = “Sunny”;
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 excute the following code?
CREATE TABLE NewTable(Column INT AUTO_INCREMENT PRIMARY KEY);
It wont raise an error . The order of AUTO_INCREMENT and the key assignment doesent matter
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 CustomerName 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]%’;
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
Also to be noted that we are using 2 tables here
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 descending 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’;
How are primary key and unique key similar?
A primary key is by default a unqiue key but a unique key is not primary key .
What is an important charecteristc of primary key?
A primary key is a key that is assigned to a column and identifies each record of a table unqiuely , that said there CAN BE only one primary key .
What happens if I execute this?
CREATE TABLE newTable4(col1 INT AUTO_INCREMENT);
It will raise me an error , because you CANT assign AUTO_INCREMENT without assining key to that column.
What happens if I execute the following?
CREATE TABLE newTable4(col1 UNIQUE KEY AUTO_INCREMENT INT);
It will raise an error because you need to mention the type of the field first before you can assign tokens such as UNQIUE KEY or AUTO_INCREMENT
What happens if I execute the following code?
SELECT DISTINCT(COL1) FROM table_name
I tiwll raise me an error.You cant use “()”
Correct syntax :
SELECT DISTINCT COL1 FROM table_name
What happens if I execute the following code?
CREATE TABLE table_name(AUTO_INCREMENT PRIMARY KEY)
It will raise and error because we havent specified column name
What happens if I excute the follwing code ?
CREATE TABLE table_name (col_name INT, PRIMARY KEY , AUTOINCERMENT)
I will raise an error because of ‘,’ in between . There should be only spaces
