Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Python > SQL Workshop > Flashcards

SQL Workshop Flashcards

1
Q

Create a table with

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

A 
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)
);
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Insert values into table the columns Column 1 and Column 2

A

INSERT INTO Table

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

SELECT DISTINCT column1, column2, …

FROM table_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Get all elements form a certain column in SQL table

A

Select column_name from table_name

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Get entries from certain column while specifying criteria

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Get elements that DO NOT satisfy specific condition in said column

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Get elements with multiple filters applied at the same time

A

SELECT * FROM Customers

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Get elments that is union of completely different search filters

A

SELECT * FROM Customers

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Get elements in Ascending order

A

SELECT * FROM Table

ORDER BY column ;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Get elements in Descedning order

A

SELECT * FROM Table

ORDER BY column DESC;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Update specific elements based on filter conditions

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

How to delete specific elements in table?

A

DELETE FROM table_name WHERE Name = “Value”;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

How to delete all records from a certain table?

A

DELETE FROM table_name;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What happens if we execute the follwing line?

DELETE Age from Table where Name=’Sunny’;

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

How to update all entries in Table?

A

UPDATE Customers

SET ContactName = ‘Alfred Schmidt’;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What happens if I exectue the following line?

Update Student_Table where Name= “Sunny”

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What are important components of update statement?

A

Udate table , SET values and condition

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What are important components of delete statement?

A

delete table and condition

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What happens if I execute the following line?

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

A

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

22
Q

How to get top 10 elements of a table

A

SELECT S.No,Name
FROM Student
LIMIT 10;

23
Q

How to get bottom 10 element

A

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

24
Q

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

A

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

25
Q

Format to calculate the statistical attributes of a table

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

Get Elements where the name starts with letter ‘a’

A

SELECT * FROM Customers

WHERE CustomerName LIKE ‘a%’;

27
Q

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

A

We should use the key words “LIKE”

28
Q

Get elements that end with letter ‘a’

A

SELECT * FROM Customers

WHERE CustomerName LIKE ‘%a’;

29
Q

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

A

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

30
Q

Get elements where the middle word is “or”

A

SELECT * FROM Customers

WHERE CustomerName LIKE ‘%or%’;

31
Q

Get element that has second word ‘r’

A

SELECT * FROM Customers

WHERE CustomerName LIKE ‘_r%’;

32
Q

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

A

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

33
Q

Get elements that start with ‘a’ and end with ‘o’

A

SELECT * FROM Customers

WHERE ContactName LIKE ‘a%o’;

34
Q

Get Customers with a CustomerName that does NOT start with “a”

A

SELECT * FROM Customers

WHERE CustomerName NOT LIKE ‘a%’;

35
Q

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

A

SELECT * FROM Customers

WHERE City LIKE ‘[bsp]%’;

36
Q

Get all customers with a City starting with “a”, “b”, “c”,”d”….till “p”

A

SELECT * FROM Customers

WHERE City LIKE ‘[a-p]%’;

37
Q

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

A

SELECT * FROM Customers

WHERE City LIKE ‘[!bsp]%’;

38
Q

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

A

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

39
Q

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

A

SELECT * FROM Customers

WHERE Country =”Germany” OR Country =”France” OR Country =”UK”

40
Q

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

A

SELECT * FROM Customers

WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

41
Q

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

A

SELECT * FROM Customers

WHERE Country NOT IN (‘Germany’, ‘France’, ‘UK’);

42
Q

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

A

SELECT * FROM Customers

WHERE NOT Country =”Germany” OR NOT Country =”France” OR NOT Country =”UK”

43
Q

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

A

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

44
Q

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

A

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

45
Q

Get all products with a price between 10 and 20

A

SELECT * FROM Products

WHERE Price BETWEEN 10 AND 20;

46
Q

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

A

SELECT * FROM Products

WHERE Price NOT BETWEEN 10 AND 20;

47
Q

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:

A

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

48
Q

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

A

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

49
Q

What happens if I try to arrange names in ascending order

example : Select name from student order by name DESC;

A

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

50
Q

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

A

SELECT * FROM Products

WHERE ProductName BETWEEN “Carnarvon Tigers” AND “Chef Anton’s Cajun Seasoning”;

51
Q

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

A

SELECT * FROM Products

WHERE ProductName BETWEEN “Carnarvon Tigers” AND “Chef Anton’s Cajun Seasoning” ORDER BY ProductName;

52
Q

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

A

SELECT * FROM Products

WHERE ProductName NOT BETWEEN ‘Carnarvon Tigers’ AND ‘Mozzarella di Giovanni’;

Python (11 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions