Se_QL Flashcards
create
CREATETABLEExperiment(
experimentIdINTNOTNULL,
PRIMARYKEY(experimentId)
);
insert
INSERTINTOExperiment(experimentId,name
VALUES(2,’Xbox’),
(3,’SharepointQuery’);
delete
drop table “tablename”
alter: add a column
ALTERTABLEExperiment
ADDpreConfiguredReportvarchar(10);
update
update tablename set colname = somevale where colname == condition
drop rows
drop
update multiple conditions
‘UPDATE Experiment set experimentStatus = ‘Error’, jobMessage = ‘{}’ where experimentId = {}’’‘.format(error, experiment_id))
insert single value
INSERT INTO table_name (col1)
VALUES (value1);
note: other rows entries become null which later can be added using “update”
rename column name in SQL server
EXEC sp_rename ‘tablename.columnanme’, ‘newcolumn_name’, ‘COLUMN’;
rename column in my sql
ALTER TABLE table_name
CHANGE COLUMN old_name TO new_name;
rename tablename in microsoft sql server
EXEC sp_rename ‘TableOldName’, ‘TableNewName
get table information schema
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=’tableName’
get table column information schema
select *
from INFORMATION_SCHEMA=
where TABLE_NAME=’tableName’
delete rows on condition
DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;
delete all records
DELETE FROM table_name;
select min or max of a column
select min/max(column name)
where tablename
note: use it as part of a where condition
set primary key
primary key can only be unique
create table(col1 int,col2 varchae, primary ket(col1))
insert with where condition ?
Insert does not accept where conditions
sql operations; row-wise ? or column-wise?
row-wise
where negate condition
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
delete table
drop tablename
delete columns
ALTER TABLE tablname DROP COLUMN col1, col2
make certain columns to index
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
drop indexname
DROP INDEX index_name on table_name
SQL
structured query language
Data models
Data models define how data is connected to each other and how they are processed and stored inside the system
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
NoSQL
can retrieve unstructured data
Entity
person, place thing or event, unique and distinct
Attribute
characteristic of an entity
Relationship and relationship types
describes association among entities
one to many
many to many
one to one
Relationship
describes association among entities
- one to many: one customer to invoices
- many to many: students to classes
- one to one: manager to store
ER diagrams
shows relationship
business process
visual representation
show links (primary keys)
primary key
column with unique id of every row
foreign key
one or more column can be used to identify a row
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.
select
select columnname from table
select col1,col2 from table;
select * from table
limit
select col1, col2
from table
limit 10
specify column names in insert
create table. Nulls and Not nulls
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);
temporary table
create temporary table tablename AS ( select * from another_table where col = "col value" )
SQL lite write access
Not available
SQL write access
available
sql comments single
select shoe_id
- -,brand_id
sql comments multiple
select shoe_id /*,brand_id ,shoe_name */ from shoes_table
editor for sql
notepad++
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.
Clauses
where, not, between, like, in, on, order by, group by
why filter?
- to get specific data
- improve query operation
- thereby improving performance
- reduce the strain on the client application
- FILTER BEFORE PULLING
how to filter
where clause
where clause operators
= <> < >= <= BETWEEN IS NULL
where equal clause
select colname1, colname2, colname3 from tablename where colname1 = 'Tofu';
*the string condition is enclosed in single quotes
string representations in sql query
full query enclosed in 1. ‘'’sql query’’’
2. “"”sql query”””
where condition enclosed in
- ’’ or
- ””
> =
<=
- applicable on only numbers
- we can also keep other columns based on the filter applied
select colname1, colname2, colname3 from tablename where colname2 >= 29;
give me everything except “Tofu”
select colname1, colname2, colname3 from tablename where colname1 <> 'Tofu';
filter for a ranges of values
select colname1, colname2, colname3 from tablename where colname2 BETWEEN 15 AND 80
IS NULL
select colname1, colname2, colname3 from tablename where colname1 IS NULL
Advanced filtering
IN, OR, NOT
IN operator
select colname1, colname2, colname3 from tablename where colname2 IN (9,10,11) ;
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
Cons of OR
ordering matters
Pros of IN
no ordering
executes faster
can add another select to it as sub query
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;
Not operator
select colname1, colname2, colname3 from tablename where NOT colname3 = 'London' AND colname3 = 'Seattle';
wildcards
Powerful pattern matching
*Like operator: can only be used with text data
how to use wildcards
Searching for text “Pizza”
- %Pizza - anything ending with Pizza
- Pizza% - anything after the word pizza
- %Pizza% - anything before and after the word pizza
- S%E: anything starts with S and ends with E
- t%@gmail.com: grabs gmail address that starts with t
can we use wildcard for null values
wildcards will not match null values
underscore wildcard
where size LIKE ‘_pizza’
output: spizza
mpizza
wildcards format/operator varies with database system
True
bracket wildcard
some RDS use this
cons of wildcards
- runs longer time
- format is different for different rds
- need to be careful while string matching implementation
why sort data
- ordering can help to interpret the data easily
2. Helpful to easy retrieve the data
rules order by
- must always be the last clause in a select statement
sorting by positions
ORDER BY 2,3
2 means second column
3 means third column
sort direction
DESC
ASC
can only be applied to column names
multiplication example
select Product Id, UintsOnOrder ,UnitPrice ,UnitsONOrder * UnitPrice AS Total_order_Cost FROM products
order of operators
parenthesis exponents multiplication division'addition subtraction
combining math operator
select Product Id, UintsOnOrder ,UnitPrice ,(UnitsOnOrder - Discount)/Quantity AS Total_Cost FROM products
Aggregate function
- Provides various ways to summarize data
- used to summarize
- find highest and lowest values
- find total no of rows
Agg functions
AVG() COUNT() MIN() MAX() SUM()
Average function
SELECT AVG(Unitprice) AS avg_price FROM products
count function
count(*)
counts all the rows in a table containing values or NULL values
select count (*) AS
total
from tablename;
count function
count(column)
counts all the rows in a specific column ignoring NULL values
select count(Colname) As
total
from tablename
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
SUM
SELECT SUM(Unitprice) AS total_price FROM products
SELECT SUM(Unitprice*UnitsInStock) AS total_price FROM products
Distinct function
- Distinct col names
- cannot use distinct with count(*)
select count(distinct CustomerId) FROM customers
Grouping data
- to summarise subset of data
- GROUPY BY |
HAVING - aggregate on particular value
- groupby can be applied for multiple columns with “,” seperator
- cannot work for aggrgated calculations
- null will be grouped if column contains null
Grouping eg
Select Region ,COUNT(CustomerID) AS total_customers FROM Customers GROUP BY Region
Clause filtering for groups
points to note
- WHERE does not work for groups
- WHERE filters on rows
- Instead use HAVING clause to filter for groups
Filter for customers having more than 2 orders
Select CustomerID ,COUNT(*) As orders From Orders Group By CustomerID HAVING COUNT (*) >= 2;
WHERE vs HAVING
- WHERE filters before data is grouped
- HAVING filters after data is grouped
- Rows eliminated by the WHERE clause will not be included in the group
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;
