Brainscape's Knowledge GenomeTM

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


See full index

Python > SQL Workshop COPY > Flashcards

SQL Workshop COPY Flashcards

1
Q

What happens if execute the following code?

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

A

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

Correct Syntax

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

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

What is the difference between unqiue Key and primary key?

A

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

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

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

A

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

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

What happens if I try to execute the following code?

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

A

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

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

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

A

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

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

What happens if I execute this ?

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

A

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

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

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

A

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

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

Get elements from certain column while specifying criteria

A

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

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

Get elements with multiple condtions are 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
14
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
15
Q

Get elements in Ascending order

A

SELECT * FROM Customers ORDER BY Country ASC;

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

Get elements in Descedning order

A

SELECT * FROM Customers ORDER BY Country DESC;

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

Update specific records of a table based on search 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
18
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
19
Q

What happens if we execute the following code?

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

A

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

How to delete specific elements in table?

A

DELETE FROM table_name WHERE Name = “Sunny”;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
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
22
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
23
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
24
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
25
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
26
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
27
Q

What are important components of delete statement?

A

delete table and condition

28
Q

What happens if I excute the following code?

CREATE TABLE NewTable(Column INT AUTO_INCREMENT PRIMARY KEY);

A

It wont raise an error . The order of AUTO_INCREMENT and the key assignment doesent matter

29
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.

30
Q

How to get top 10 elements of a table

A

SELECT S.No,Name FROM Student LIMIT 10;

31
Q

How to get bottom 10 element

A

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

32
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

33
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

34
Q

Get Elements where the name starts with letter ‘a’

A

SELECT * FROM Customers WHERE CustomerName LIKE ‘a%’;

35
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”

36
Q

Get elements that end with letter ‘a’

A

SELECT * FROM Customers WHERE CustomerName LIKE ‘%a’;

37
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

38
Q

Get elements where the middle word is “or”

A

SELECT * FROM Customers WHERE CustomerName LIKE ‘%or%’;

39
Q

Get element that has second word ‘r’

A

SELECT * FROM Customers WHERE CustomerName LIKE ‘_r%’;

40
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

41
Q

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

A

SELECT * FROM Customers WHERE ContactName LIKE ‘a%o’;

42
Q

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

A

SELECT * FROM Customers WHERE CustomerName NOT LIKE ‘a%’;

43
Q

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

A

SELECT * FROM Customers WHERE CustomerName LIKE ‘[bsp]%’;

44
Q

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

A

SELECT * FROM Customers WHERE City LIKE ‘[a-p]%’;

45
Q

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

A

SELECT * FROM Customers WHERE City LIKE ‘[!bsp]%’;

46
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”

47
Q

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

A

SELECT * FROM Customers WHERE Country IN (‘Germany’, ‘France’, ‘UK’);

48
Q

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

A

SELECT * FROM Customers WHERE Country NOT IN (‘Germany’, ‘France’, ‘UK’);

49
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”

50
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.

51
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

Also to be noted that we are using 2 tables here

52
Q

Get all products with a price between 10 and 20

A

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

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

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

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

56
Q

What happens if I try to arrange names in descending 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

57
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”;

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

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

60
Q

How are primary key and unique key similar?

A

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

61
Q

What is an important charecteristc of primary key?

A

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 .

62
Q

What happens if I execute this?

CREATE TABLE newTable4(col1 INT AUTO_INCREMENT);

A

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

63
Q

What happens if I execute the following?

CREATE TABLE newTable4(col1 UNIQUE KEY AUTO_INCREMENT INT);

A

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

64
Q

What happens if I execute the following code?

SELECT DISTINCT(COL1) FROM table_name

A

I tiwll raise me an error.You cant use “()”

Correct syntax :

SELECT DISTINCT COL1 FROM table_name

65
Q

What happens if I execute the following code?

CREATE TABLE table_name(AUTO_INCREMENT PRIMARY KEY)

A

It will raise and error because we havent specified column name

66
Q

What happens if I excute the follwing code ?

CREATE TABLE table_name (col_name INT, PRIMARY KEY , AUTOINCERMENT)

A

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

Python (11 decks)

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