Se_QL Flashcards
1
Q
create
A
CREATETABLEExperiment(
experimentIdINTNOTNULL,
PRIMARYKEY(experimentId)
);
2
Q
insert
A
INSERTINTOExperiment(experimentId,name
VALUES(2,’Xbox’),
(3,’SharepointQuery’);
3
Q
delete
A
drop table “tablename”
4
Q
alter: add a column
A
ALTERTABLEExperiment
ADDpreConfiguredReportvarchar(10);
5
Q
update
A
update tablename set colname = somevale where colname == condition
6
Q
drop rows
A
drop
7
Q
update multiple conditions
A
‘UPDATE Experiment set experimentStatus = ‘Error’, jobMessage = ‘{}’ where experimentId = {}’’‘.format(error, experiment_id))
8
Q
insert single value
A
INSERT INTO table_name (col1)
VALUES (value1);
note: other rows entries become null which later can be added using “update”
9
Q
rename column name in SQL server
A
EXEC sp_rename ‘tablename.columnanme’, ‘newcolumn_name’, ‘COLUMN’;
10
Q
rename column in my sql
A
ALTER TABLE table_name
CHANGE COLUMN old_name TO new_name;
11
Q
rename tablename in microsoft sql server
A
EXEC sp_rename ‘TableOldName’, ‘TableNewName
12
Q
get table information schema
A
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=’tableName’
13
Q
get table column information schema
A
select *
from INFORMATION_SCHEMA=
where TABLE_NAME=’tableName’
14
Q
delete rows on condition
A
DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;
15
Q
delete all records
A
DELETE FROM table_name;
16
Q
select min or max of a column
A
select min/max(column name)
where tablename
note: use it as part of a where condition
17
Q
set primary key
A
primary key can only be unique
create table(col1 int,col2 varchae, primary ket(col1))
18
Q
insert with where condition ?
A
Insert does not accept where conditions
19
Q
sql operations; row-wise ? or column-wise?
A
row-wise
20
Q
where negate condition
A
where “column name” IS NOT Null
where “column name” NOT like “result”
where “col name” < 3000 or “col name” > 5000
Note: other complex conditions needs to be added
21
Q
delete table
A
drop tablename
22
Q
delete columns
A
ALTER TABLE tablname DROP COLUMN col1, col2
23
Q
make certain columns to index
A
CREATE INDEX indexname ON tablename (col1,col2....)
- this sorts the column values making it easier while fetching information
- runtime was reduced considerably from 14 to 2 mins
24
Q
drop indexname
A
DROP INDEX index_name on table_name
25
SQL
structured query language
26
Data models
Data models define how data is connected to each other and how they are processed and stored inside the system
27
ER models
Entity–relationship model
An entity–relationship model describes interrelated things of interest in a specific domain of knowledge. A basic ER model is composed of entity types and specifies relationships that can exist between entities
28
NoSQL
can retrieve unstructured data
29
Entity
person, place thing or event, unique and distinct
30
Attribute
characteristic of an entity
31
Relationship and relationship types
describes association among entities
one to many
many to many
one to one
32
Relationship
describes association among entities
1. one to many: one customer to invoices
2. many to many: students to classes
3. one to one: manager to store
33
ER diagrams
shows relationship
business process
visual representation
show links (primary keys)
34
primary key
column with unique id of every row
35
foreign key
one or more column can be used to identify a row
36
types of ER diagrams
We have the Chen notation, and there's the Crow's foot notation, and then there's the UML class diagram notation.
37
select
select columnname from table
select col1,col2 from table;
select * from table
38
limit
select col1, col2
from table
limit 10
39
create table. Nulls and Not nulls
#specify column names in insert
```
create table tablename (i
id int primary key,
col2 varchar(100) Not null,
col3 char(10) Null);
```
insert into table (col1,col2,col3)
values (val1, val2 val3),
(val1, val2 ,val3);
40
temporary table
```
create temporary table tablename AS
(
select *
from another_table
where col = "col value"
)
```
41
SQL lite write access
Not available
42
SQL write access
available
43
sql comments single
select shoe_id
| - -,brand_id
44
sql comments multiple
```
select shoe_id
/*,brand_id
,shoe_name
*/
from shoes_table
```
45
editor for sql
notepad++
46
sql lite, real datatype
A real data type is a data type used in a computer program to represent an approximation of a real number.
47
Clauses
where, not, between, like, in, on, order by, group by
48
why filter?
* to get specific data
* improve query operation
* thereby improving performance
* reduce the strain on the client application
* FILTER BEFORE PULLING
49
how to filter
where clause
50
where clause operators
```
=
<>
<
>=
<=
BETWEEN
IS NULL
```
51
where equal clause
```
select colname1,
colname2,
colname3
from tablename
where colname1 = 'Tofu';
```
*the string condition is enclosed in single quotes
52
string representations in sql query
full query enclosed in 1. '''sql query'''
2. """sql query"""
where condition enclosed in
1. '' or
2. ""
53
>=
<=
* applicable on only numbers
* we can also keep other columns based on the filter applied
```
select colname1,
colname2,
colname3
from tablename
where colname2 >= 29;
```
54
give me everything except "Tofu"
```
select colname1,
colname2,
colname3
from tablename
where colname1 <> 'Tofu';
```
55
filter for a ranges of values
```
select colname1,
colname2,
colname3
from tablename
where colname2 BETWEEN 15 AND 80
```
56
IS NULL
```
select colname1,
colname2,
colname3
from tablename
where colname1 IS NULL
```
57
Advanced filtering
IN, OR, NOT
58
IN operator
```
select colname1,
colname2,
colname3
from tablename
where colname2 IN (9,10,11) ;
```
59
OR operator
```
select colname1,
colname2,
colname3
from tablename
where colname1 = 'Tofu' OR 'Konbu';
```
Important Note: If the operator finds "Tofu" first then it will stop, "Konbu" will not be filtered
60
Cons of OR
ordering matters
61
Pros of IN
no ordering
executes faster
can add another select to it as sub query
62
Or and AND
What is important to have??
Important: Have paranethesis
```
select colname1,
colname2,
colname3
from tablename
where (colname1 = 'Tofu' OR 'Konbu'),
AND colname2 = 12;
```
63
Not operator
```
select colname1,
colname2,
colname3
from tablename
where NOT colname3 = 'London'
AND colname3 = 'Seattle';
```
64
wildcards
Powerful pattern matching
*Like operator: can only be used with text data
65
how to use wildcards
Searching for text "Pizza"
1. %Pizza - anything ending with Pizza
2. Pizza% - anything after the word pizza
3. %Pizza% - anything before and after the word pizza
4. S%E: anything starts with S and ends with E
5. t%@gmail.com: grabs gmail address that starts with t
66
can we use wildcard for null values
wildcards will not match null values
67
underscore wildcard
where size LIKE '_pizza'
output: spizza
mpizza
68
wildcards format/operator varies with database system
True
69
bracket wildcard
some RDS use this
70
cons of wildcards
1. runs longer time
2. format is different for different rds
3. need to be careful while string matching implementation
71
why sort data
1. ordering can help to interpret the data easily
| 2. Helpful to easy retrieve the data
72
rules order by
1. must always be the last clause in a select statement
73
sorting by positions
ORDER BY 2,3
2 means second column
3 means third column
74
sort direction
DESC
ASC
can only be applied to column names
75
multiplication example
```
select
Product Id,
UintsOnOrder
,UnitPrice
,UnitsONOrder * UnitPrice AS Total_order_Cost
FROM products
```
76
order of operators
```
parenthesis
exponents
multiplication
division'addition
subtraction
```
77
combining math operator
```
select
Product Id,
UintsOnOrder
,UnitPrice
,(UnitsOnOrder - Discount)/Quantity AS Total_Cost
FROM products
```
78
Aggregate function
1. Provides various ways to summarize data
2. used to summarize
3. find highest and lowest values
4. find total no of rows
79
Agg functions
```
AVG()
COUNT()
MIN()
MAX()
SUM()
```
80
Average function
```
SELECT AVG(Unitprice) AS avg_price
FROM products
```
81
count function
| count(*)
counts all the rows in a table containing values or NULL values
select count (*) AS
total
from tablename;
82
count function
| count(column)
counts all the rows in a specific column ignoring NULL values
select count(Colname) As
total
from tablename
83
Max and MIN func
```
select MAX(unitprice) AS max_prod_price
from products
```
```
select MAX(unitprice) AS max_prod_price, min(unitprice) ASmin_prod_proce
from products
```
** NUll value will be ignored in this case
84
SUM
```
SELECT SUM(Unitprice) AS total_price
FROM products
```
```
SELECT SUM(Unitprice*UnitsInStock) AS total_price
FROM products
```
85
Distinct function
* Distinct col names
* cannot use distinct with count(*)
```
select count(distinct CustomerId)
FROM customers
```
86
Grouping data
1. to summarise subset of data
2. GROUPY BY |
HAVING
3. aggregate on particular value
4. groupby can be applied for multiple columns with "," seperator
5. cannot work for aggrgated calculations
6. null will be grouped if column contains null
87
Grouping eg
```
Select
Region
,COUNT(CustomerID) AS total_customers
FROM Customers
GROUP BY Region
```
88
Clause filtering for groups
points to note
1. WHERE does not work for groups
2. WHERE filters on rows
3. Instead use HAVING clause to filter for groups
89
Filter for customers having more than 2 orders
```
Select
CustomerID
,COUNT(*) As orders
From Orders
Group By CustomerID
HAVING COUNT (*) >= 2;
```
90
WHERE vs HAVING
1. WHERE filters before data is grouped
2. HAVING filters after data is grouped
3. Rows eliminated by the WHERE clause will not be included in the group
91
Order by with
| Group by
ORDER BY sorts data
GROUP BY does not sort data
```
Select
SupplierID
,COUNT(*) As Num_Prod
From Produxts
WHERE UNitPrice >=4
Group By SupplierID
HAVING COUNT (*) >= 2;
```
