SQL Workshop COPY

Question 1

What happens if execute the following code?

Create newTable(col1 INT, col2 CHAR(25));

Answer 1

It will raise me error because you forgot to mention the keyword table .

Correct Syntax

Create table newTable(col1 INT, col2 CHAR(25);

Question 2

What is the difference between unqiue Key and primary key?

Answer 2

There may be many unique key columns in a table but there can be only ONE primary key column.

Question 3

What happens when I assign a certain column as Unique key?

Answer 3

It makes sure that all the entries in that column are unique and has no duplicates

Question 4

Create a table with 1)Auto increment 2)uinique primary key 3)char , int and date-time columns .

Answer 4

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) );

Question 5

What happens if I try to execute the following code?

CREATE TABLE NewTable(S.No INT PRIMARY KEY);

Answer 5

It will raise and error because ‘.’ is not allowed for COLUMN name assingment. ‘_’ Is allowed.So the appropriate word would be ‘S_No’

Question 6

Insert values into table that has columns ‘Column1’(INT) and ‘Column2’(string)

Answer 6

INSERT INTO Table_Name (Column1, Column2) VALUES (001, ‘Monika’)

Question 7

What happens if I execute this ?

INSERT INTO TABLE newtable3(col1,col2,col3) VALUES (1,2,’Hello’);

Answer 7

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’);

Question 8

What is the differnce between create and Insert in terms of syntax?

Answer 8

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

Question 9

Get all unique elements from duplicates in certain column of sql table

Answer 9

SELECT DISTINCT column1, column2, … FROM table_name;

Question 10

Get all elements form a certain column in SQL table

Answer 10

Select column_name from table_name

Question 11

Get elements from certain column while specifying criteria

Answer 11

SELECT column1, column2, … FROM table_name WHERE column1>thresh_val;

Question 12

Get elements that DO NOT satisfy specific condition in said column

Answer 12

SELECT column1, column2, … FROM table_name WHERE NOT colum1=”Sunny”;

Question 13

Get elements with multiple condtions are applied at the same time

Answer 13

SELECT * FROM Customers WHERE Country=’Germany’ AND City=’Berlin’;

Question 14

Get elments that is union of completely different search filters

Answer 14

SELECT * FROM Customers WHERE Country=’Germany’ OR Country=’Spain’;

Question 15

Get elements in Ascending order

Answer 15

SELECT * FROM Customers ORDER BY Country ASC;

Question 16

Get elements in Descedning order

Answer 16

SELECT * FROM Customers ORDER BY Country DESC;

Question 17

Update specific records of a table based on search conditions

Answer 17

UPDATE Customers SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’ WHERE CustomerID = 1;

Question 18

What happens if I do this? UPDATE Customers SET ContactName=’Juan’;

Answer 18

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.

Question 19

What happens if we execute the following code?

UPDATE Customers SET ContactName = ‘Amos’ AND Country = ‘India’ WHERE CustomerID = 1;

Answer 19

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;

Question 20

How to delete specific elements in table?

Answer 20

DELETE FROM table_name WHERE Name = “Sunny”;

Question 21

What is one important warning to remember while deleting entries in SQL ?

Answer 21

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.

Question 22

How to delete all records from a certain table?

Answer 22

DELETE FROM table_name;

Question 23

What happens if we execute the follwing line? DELETE Age from Table where Name=’Sunny’;

Answer 23

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.

Question 24

How to update all entries in Table?

Answer 24

UPDATE Customers SET ContactName = ‘Alfred Schmidt’;

Question 25

What happens if I exectue the following line? Update Student\_Table where Name= "Sunny"

Answer 25

It will raise me an error because we havent specified the "set" component in update.

Question 26

What are important components of update statement?

Answer 26

Udate table , SET values and condition

Question 27

What are important components of delete statement?

Answer 27

delete table and condition

Question 28

What happens if I excute the following code? CREATE TABLE NewTable(Column INT AUTO\_INCREMENT PRIMARY KEY);

Answer 28

It wont raise an error . The order of AUTO\_INCREMENT and the key assignment doesent matter

Question 29

What happens if I execute the following line? Update Student\_Table set Age = 20 where "Name" = Sunny

Answer 29

It will raise me an error because the field should not be a string and the value its checking (Sunny) should be a string.

Question 30

How to get top 10 elements of a table

Answer 30

SELECT S.No,Name FROM Student LIMIT 10;

Question 31

How to get bottom 10 element

Answer 31

SELECT S.No,Name FROM Student ORDER by S.No DESC LIMIT 10;

Question 32

What type of statistical attributes can we calculate from numerical fields in data table?

Answer 32

We can calclulate , COUNT , MIN , MAX, SUM AND AVG

Question 33

Format to calculate the statistical attributes of a table

Answer 33

Select MIN(Age) from Students where City = "Frankfurt" We can use the same for COUNT , MAX, SUM or AVG

Question 34

Get Elements where the name starts with letter 'a'

Answer 34

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

Question 35

What key word should i use in case I want to find words that follow a certain pattern?

Answer 35

We should use the key words "LIKE"

Question 36

Get elements that end with letter 'a'

Answer 36

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

Question 37

What happens if I use the following syntax? SELECT \* FROM Customers WHERE CustomerName LIKE '\*a';

Answer 37

This will raise me and error if I am using mySQL . But that wont be the case if we are using MS Access

Question 38

Get elements where the middle word is "or"

Answer 38

SELECT \* FROM Customers WHERE CustomerName LIKE '%or%';

Question 39

Get element that has second word 'r'

Answer 39

SELECT \* FROM Customers WHERE CustomerName LIKE '\_r%';

Question 40

Get elements that has starting letter 'a' and has atleast 3 words in the string

Answer 40

SELECT \* FROM Customers WHERE CustomerName LIKE 'a\_\_%'; Note: There are 2 '\_' in the syntax above

Question 41

Get elements that start with 'a' and end with 'o'

Answer 41

SELECT \* FROM Customers WHERE ContactName LIKE 'a%o';

Question 42

Get Customers with a CustomerName that does NOT start with "a"

Answer 42

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

Question 43

Get all customers with a City starting with "b", "s", or "p"

Answer 43

SELECT \* FROM Customers WHERE CustomerName LIKE '[bsp]%';

Question 44

Get all customers with a City starting with "a", "b", "c","d"....till "p"

Answer 44

SELECT \* FROM Customers WHERE City LIKE '[a-p]%';

Question 45

Get all customers with a City NOT starting with "b", "s", or "p"

Answer 45

SELECT \* FROM Customers WHERE City LIKE '[!bsp]%';

Question 46

Get all customers that are located in "Germany", "France" or "UK"

Answer 46

SELECT \* FROM Customers WHERE Country ="Germany" OR Country ="France" OR Country ="UK"

Question 47

What is an alternative for getting customers from "Germany", "France" or "UK"

Answer 47

SELECT \* FROM Customers WHERE Country IN ('Germany', 'France', 'UK');

Question 48

Get all customers that are NOT located in "Germany", "France" or "UK"

Answer 48

SELECT \* FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK');

Question 49

Alternative for getting customers that are not located in "Germany", "France" or "UK"

Answer 49

SELECT \* FROM Customers WHERE NOT Country ="Germany" OR NOT Country ="France" OR NOT Country ="UK"

Question 50

Why do we use IN functionality when we could use OR logic or other logical expressions to get our results

Answer 50

Because we cant always cover ALL checking conditions . Sometimes there might be too many to cover ."IN" can be useful in situations like these.

Question 51

Get all customers that are from the same countries as the suppliers

Answer 51

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

Question 52

Get all products with a price between 10 and 20

Answer 52

SELECT \* FROM Products WHERE Price BETWEEN 10 AND 20;

Question 53

Get all products outside a specific range like say less than 10 or greater than 20

Answer 53

SELECT \* FROM Products WHERE Price NOT BETWEEN 10 AND 20;

Question 54

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:

Answer 54

SELECT \* FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3);

Question 55

What happens if I execute the following syntax? SELECT \* FROM Customers WHERE Country NOT IN ['Germany', 'France', 'UK'];

Answer 55

It raises me an error , Square brackets are not allowed .It should be ('Germany', 'France', 'UK');

Question 56

What happens if I try to arrange names in descending order example : Select name from student order by name DESC;

Answer 56

It will arrange in all names in opposite chronological order , Example : Z to A Manjimup , Lousiana , Herbert etc

Question 57

Get all products with a ProductName BETWEEN Carnarvon Tigers and Chef Anton's Cajun Seasoning

Answer 57

SELECT \* FROM Products WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning";

Question 58

Get all products with a ProductName BETWEEN Carnarvon Tigers and Chef Anton's Cajun Seasoning WHILE maintaining chronological order

Answer 58

SELECT \* FROM Products WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning" ORDER BY ProductName;

Question 59

Get all products with a ProductName NOT BETWEEN Carnarvon Tigers and Mozzarella di Giovanni

Answer 59

SELECT \* FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni';

Question 60

How are primary key and unique key similar?

Answer 60

A primary key is by default a unqiue key but a unique key is not primary key .

Question 61

What is an important charecteristc of primary key?

Answer 61

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 .

Question 62

What happens if I execute this? CREATE TABLE newTable4(col1 INT AUTO\_INCREMENT);

Answer 62

It will raise me an error , because you CANT assign AUTO\_INCREMENT without assining key to that column.

Question 63

What happens if I execute the following? CREATE TABLE newTable4(col1 UNIQUE KEY AUTO\_INCREMENT INT);

Answer 63

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

Question 64

What happens if I execute the following code? SELECT DISTINCT(COL1) FROM table\_name

Answer 64

I tiwll raise me an error.You cant use "()" Correct syntax : SELECT DISTINCT COL1 FROM table\_name

Question 65

What happens if I execute the following code? CREATE TABLE table\_name(AUTO\_INCREMENT PRIMARY KEY)

Answer 65

It will raise and error because we havent specified column name

Question 66

What happens if I excute the follwing code ? CREATE TABLE table\_name (col\_name INT, PRIMARY KEY , AUTOINCERMENT)

Answer 66

I will raise an error because of ',' in between . There should be only spaces