SQL Workshop

Question 1

Create a table with

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

Answer 1

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 2

Insert values into table the columns Column 1 and Column 2

Answer 2

INSERT INTO Table

(Column1, Column2) VALUES (001, ‘Monika’)

Question 3

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

Answer 3

SELECT DISTINCT column1, column2, …

FROM table_name;

Question 4

Get all elements form a certain column in SQL table

Answer 4

Select column_name from table_name

Question 5

Get entries from certain column while specifying criteria

Answer 5

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

Question 6

Get elements that DO NOT satisfy specific condition in said column

Answer 6

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

Question 7

Get elements with multiple filters applied at the same time

Answer 7

SELECT * FROM Customers

WHERE Country=’Germany’ AND City=’Berlin’;

Question 8

Get elments that is union of completely different search filters

Answer 8

SELECT * FROM Customers

WHERE Country=’Germany’ OR Country=’Spain’;

Question 9

Get elements in Ascending order

Answer 9

SELECT * FROM Table

ORDER BY column ;

Question 10

Get elements in Descedning order

Answer 10

SELECT * FROM Table

ORDER BY column DESC;

Question 11

Update specific elements based on filter conditions

Answer 11

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

Question 12

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

Answer 12

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 13

How to delete specific elements in table?

Answer 13

DELETE FROM table_name WHERE Name = “Value”;

Question 14

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

Answer 14

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 15

How to delete all records from a certain table?

Answer 15

DELETE FROM table_name;

Question 16

What happens if we execute the follwing line?

DELETE Age from Table where Name=’Sunny’;

Answer 16

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 17

How to update all entries in Table?

Answer 17

UPDATE Customers

SET ContactName = ‘Alfred Schmidt’;

Question 18

What happens if I exectue the following line?

Update Student_Table where Name= “Sunny”

Answer 18

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

Question 19

What are important components of update statement?

Answer 19

Udate table , SET values and condition

Question 20

What are important components of delete statement?

Answer 20

delete table and condition

Question 21

What happens if I execute the following line?

Update Student_Table set Age = 20 where “Name” = Sunny

Answer 21

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 22

How to get top 10 elements of a table

Answer 22

SELECT S.No,Name
FROM Student
LIMIT 10;

Question 23

How to get bottom 10 element

Answer 23

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

Question 24

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

Answer 24

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

Question 25

Format to calculate the statistical attributes of a table

Answer 25

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

Question 26

Get Elements where the name starts with letter 'a'

Answer 26

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

Question 27

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

Answer 27

We should use the key words "LIKE"

Question 28

Get elements that end with letter 'a'

Answer 28

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

Question 29

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

Answer 29

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

Question 30

Get elements where the middle word is "or"

Answer 30

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

Question 31

Get element that has second word 'r'

Answer 31

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

Question 32

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

Answer 32

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

Question 33

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

Answer 33

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

Question 34

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

Answer 34

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

Question 35

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

Answer 35

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

Question 36

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

Answer 36

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

Question 37

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

Answer 37

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

Question 38

What happens if I exectue the following syntax? SELECT * FROM Customers WHERE City LIKE '![bsp]%';

Answer 38

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

Question 39

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

Answer 39

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

Question 40

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

Answer 40

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

Question 41

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

Answer 41

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

Question 42

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

Answer 42

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

Question 43

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

Answer 43

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 44

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

Answer 44

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

Question 45

Get all products with a price between 10 and 20

Answer 45

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

Question 46

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

Answer 46

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

Question 47

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 47

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

Question 48

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

Answer 48

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

Question 49

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

Answer 49

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

Question 50

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

Answer 50

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

Question 51

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

Answer 51

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

Question 52

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

Answer 52

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