Pandas and SQL Flashcards

Question 1

Q

What happens if execute the following code?

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

Answer

A

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

Q

What is the difference between unqiue Key and primary key?

Answer

A

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

Question 3

Q

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

Answer

A

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

Question 4

Q

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

Answer

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

Question 5

Q

What happens if I try to execute the following code?

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

Answer

A

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

Q

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

Answer

A

INSERT INTO Table_Name (Column1, Column2) VALUES (001, 'Monika')

Question 7

Q

What happens if I execute this ?

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

Answer

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

Question 8

Q

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

Answer

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

Question 9

Q

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

Answer

A

SELECT DISTINCT column1, column2, ...FROM table_name;

Question 10

Q

Get all elements form a certain column in SQL table

Answer

A

Select column_name from table_name

Question 11

Q

Get elements from certain column while specifying criteria SQL

Answer

A

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

Question 12

Q

Get elements that DO NOT satisfy specific condition in said column

Answer

A

SELECT column1, column2, ...FROM table_nameWHERE NOT colum1="Sunny";

Question 13

Q

Get elements with multiple condtions are applied at the same time

Answer

A

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

Question 14

Q

Get elments that is union of completely different search filters

Answer

A

SELECT * FROM CustomersWHERE Country='Germany' OR Country='Spain';

Question 15

Q

Get elements in Ascending order

Answer

A

SELECT * FROM CustomersORDER BY Country ASC;

Question 16

Q

Get elements in Descedning order

Answer

A

SELECT * FROM CustomersORDER BY Country DESC;

Question 17

Q

Update specific records of a table based on search conditions

Answer

A

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

Question 18

Q

What happens if I do this?UPDATE CustomersSET ContactName='Juan';

Answer

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.

Question 19

Q

What happens if we execute the following code?

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

Answer

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;

Question 20

Q

How to delete specific elements in table?

Answer

A

DELETE FROM table_name WHERE Name = "Sunny";

Question 21

Q

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

Answer

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.

Question 22

Q

How to delete all records from a certain table?

Answer

A

DELETE FROM table_name;

Question 23

Q

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

Answer

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.

Question 24

Q

How to update all entries in Table?

Answer

A

UPDATE CustomersSET ContactName = 'Alfred Schmidt';

Question 25

Q

What happens if I exectue the following line?Update Student_Table where Name= "Sunny"

Answer

A

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

Question 26

Q

What are important components of update statement?

Answer

A

Udate table , SET values and condition

Question 27

Q

What are important components of delete statement?

Answer

A

delete table and condition

Question 28

Q

What happens if I excute the following code?

CREATE TABLE NewTable(Column INT AUTO_INCREMENT PRIMARY KEY);

Answer

A

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

Question 29

Q

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

Answer

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.

Question 30

Q

How to get top 10 elements of a table

Answer

A

SELECT S.No,NameFROM StudentLIMIT 10;

Question 31

Q

How to get bottom 10 element

Answer

A

SELECT S.No,NameFROM Student ORDER by S.No DESCLIMIT 10;

Question 32

Q

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

Answer

A

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

Question 33

Q

Format to calculate the statistical attributes of a table

Answer

A

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

Question 34

Q

Get Elements where the name starts with letter 'a'

Answer

A

SELECT * FROM CustomersWHERE CustomerName LIKE 'a%';

Question 35

Q

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

Answer

A

We should use the key words "LIKE"

Question 36

Q

Get elements that end with letter 'a'

Answer

A

SELECT * FROM CustomersWHERE CustomerName LIKE '%a';

Question 37

Q

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

Answer

A

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

Q

Get elements where the middle word is "or"

Answer

A

SELECT * FROM CustomersWHERE CustomerName LIKE '%or%';

Question 39

Q

Get element that has second word 'r'

Answer

A

SELECT * FROM CustomersWHERE CustomerName LIKE '_r%';

Question 40

Q

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

Answer

A

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

Question 41

Q

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

Answer

A

SELECT * FROM CustomersWHERE ContactName LIKE 'a%o';

Question 42

Q

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

Answer

A

SELECT * FROM CustomersWHERE CustomerName NOT LIKE 'a%';

Question 43

Q

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

Answer

A

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

Question 44

Q

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

Answer

A

SELECT * FROM CustomersWHERE City LIKE '[a-p]%';

Question 45

Q

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

Answer

A

SELECT * FROM CustomersWHERE City LIKE '[!bsp]%';

Question 46

Q

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

Answer

A

SELECT * FROM CustomersWHERE Country ="Germany" OR Country ="France" OR Country ="UK"

Question 47

Q

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

Answer

A

SELECT * FROM CustomersWHERE Country IN ('Germany', 'France', 'UK');

Question 48

Q

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

Answer

A

SELECT * FROM CustomersWHERE Country NOT IN ('Germany', 'France', 'UK');

Question 49

Q

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

Answer

A

SELECT * FROM CustomersWHERE NOT Country ="Germany" OR NOT Country ="France" OR NOT Country ="UK"

Question 50

Q

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

Answer

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.

Question 51

Q

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

Answer

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

Question 52

Q

Get all products with a price between 10 and 20

Answer

A

SELECT * FROM ProductsWHERE Price BETWEEN 10 AND 20;

Question 53

Q

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

Answer

A

SELECT * FROM ProductsWHERE Price NOT BETWEEN 10 AND 20;

Question 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:

Answer

A

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

Question 55

Q

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

Answer

A

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

Question 56

Q

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

Answer

A

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

Question 57

Q

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

Answer

A

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

Question 58

Q

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

Answer

A

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

Question 59

Q

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

Answer

A

SELECT * FROM ProductsWHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni';

Question 60

Q

How are primary key and unique key similar?

Answer

A

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

Question 61

Q

What is an important charecteristc of primary key?

Answer

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 .

Question 62

Q

What happens if I execute this?

CREATE TABLE newTable4(col1 INT AUTO_INCREMENT);

Answer

A

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

Question 63

Q

What happens if I execute the following?

CREATE TABLE newTable4(col1 UNIQUE KEY AUTO_INCREMENT INT);

Answer

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

Question 64

Q

What happens if I execute the following code?

SELECT DISTINCT(COL1) FROM table_name

Answer

A

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

Correct syntax :

SELECT DISTINCT COL1 FROM table_name

Answer 65

A

It will raise and error because we havent specified column name

Answer 66

A

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

Answer 67

A

import pandas as pd

df=pd.read_csv("k.csv")

Answer 68

A

Prints the first 3 entires

Answer 69

A

Fetches the last 3 entries of all entries

Answer 70

A

excel_data_df = pandas.read_excel('records.xlsx', sheet_name='Employees')

Answer 71

A

df = pd.read_csv("pk_data.txt".delimiter='\t' )

Answer 72

A

df.columns

Answer 73

A

dt['Col_Name']

Answer 74

A

dt['Col'][0:5]

Answer 75

A

dt[['Col1','Col2']]

Answer 76

A

df.iloc[1]

(Getting entry at position 2 zero indexing applies)

Answer 77

A

df.iloc[0:2]

Answer 78

A

df.iloc[1,2]

1 is the second entry

And 2 stands for the 3rd column (We have to account for the zero indexing)

Answer 79

A

for index,df in df.iterrows(): print(index,df)

/////df stands for data frame/////

Answer 80

A

for index,df in df.iterrows(): print(index,df['Name'])

Answer 81

A

df.loc[df['Type 1'] == Grass]

df.loc['Type 1'] returns all data strings under Type 1 column and then the condition checks which of them are 'Grass'

Answer 82

A

df.loc[df['Type 1'] == Grass].loc[df['Type 1'] == Fire]]

Answer 83

A

df['Age'].describe()['std']

Answer 84

A

It displays data like count ,mean , min , std, 25percent, 75 and max

Answer 85

A

df.sort_values('Power')

Answer 86

A

df.sort_values('Name')

Answer 87

A

df.sort_values('Name',ascending = False)

Answer 88

A

df.sort_values(['Type1','HP'],ascending=[1,0])

Answer 89

A

df['Total'] = df['HP']+df['Attack']-df['Damage']

/////It automatically creates a new column for the rest ////

Answer 90

A

df = df.drop(columns = ['Total1','Total2'])

Answer 91

A

df["Age"].describe()

Answer 92

A

It returns me a dictionary of various quantites

Answer 93

A

first = data.iloc[0]['Age']

We used 0 as index for entry (Meaning first row ) and label as identifier for specific column (Age is the column)

Answer 94

A

df.describe()['std']

Answer 95

A

It will raise an error because in df[Name] we should pass a stiring, not just Name(Which might work if it was defined as a string var)

Answer 96

A

loc is used for fetching entires based on boolean conditions

whereas

iloc is used for fetching results based on simple indices.

(Hence the use of i, which probably means index locator)

Answer 97

A

df.loc[df.iloc[:,6]

Answer 98

A

df.loc[df['Col1']

Answer 99

A

df.loc[df['Units']

Answer 100

A

df.replace(to_replace ="Boston Celtics",value ="Omega Warrior")

Answer 101

A

df.replace(to_replace =["Boston Celtics", "Texas"], value ="Omega Warrior")

Brainscape's Knowledge GenomeTM

Pandas and SQL Flashcards

Brainscape's Knowledge Genome^TM