Brainscape's Knowledge GenomeTM

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


See full index

Forte > Se_QL > Flashcards

Se_QL Flashcards

1
Q

create

A

CREATETABLEExperiment(
experimentIdINTNOTNULL,
PRIMARYKEY(experimentId)
);

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

insert

A

INSERTINTOExperiment(experimentId,name
VALUES(2,’Xbox’),
(3,’SharepointQuery’);

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

delete

A

drop table “tablename”

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

alter: add a column

A

ALTERTABLEExperiment

ADDpreConfiguredReportvarchar(10);

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

update

A

update tablename set colname = somevale where colname == condition

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

drop rows

A

drop

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

update multiple conditions

A

‘UPDATE Experiment set experimentStatus = ‘Error’, jobMessage = ‘{}’ where experimentId = {}’’‘.format(error, experiment_id))

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

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

rename column name in SQL server

A

EXEC sp_rename ‘tablename.columnanme’, ‘newcolumn_name’, ‘COLUMN’;

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

rename column in my sql

A

ALTER TABLE table_name

CHANGE COLUMN old_name TO new_name;

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

rename tablename in microsoft sql server

A

EXEC sp_rename ‘TableOldName’, ‘TableNewName

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

get table information schema

A

select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME=’tableName’

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

get table column information schema

A

select *
from INFORMATION_SCHEMA=
where TABLE_NAME=’tableName’

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

delete rows on condition

A

DELETE FROM Customers WHERE CustomerName=’Alfreds Futterkiste’;

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

delete all records

A

DELETE FROM table_name;

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

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

set primary key

A

primary key can only be unique

create table(col1 int,col2 varchae, primary ket(col1))

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

insert with where condition ?

A

Insert does not accept where conditions

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

sql operations; row-wise ? or column-wise?

A

row-wise

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

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

delete table

A

drop tablename

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

delete columns

A

ALTER TABLE tablname DROP COLUMN col1, col2

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

drop indexname

A

DROP INDEX index_name on table_name

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

SQL

A

structured query language

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

Data models

A

Data models define how data is connected to each other and how they are processed and stored inside the system

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

ER models

A

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

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

NoSQL

A

can retrieve unstructured data

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

Entity

A

person, place thing or event, unique and distinct

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

Attribute

A

characteristic of an entity

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

Relationship and relationship types

A

describes association among entities

one to many
many to many
one to one

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

Relationship

A

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

ER diagrams

A

shows relationship
business process
visual representation
show links (primary keys)

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

primary key

A

column with unique id of every row

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

foreign key

A

one or more column can be used to identify a row

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

types of ER diagrams

A

We have the Chen notation, and there’s the Crow’s foot notation, and then there’s the UML class diagram notation.

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

select

A

select columnname from table
select col1,col2 from table;
select * from table

38
Q

limit

A

select col1, col2
from table
limit 10

39
Q

specify column names in insert

create table. Nulls and Not nulls

A 
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
Q

temporary table

A 
create temporary table tablename AS
(
select *
from another_table
where col = "col value"
)
41
Q

SQL lite write access

A

Not available

42
Q

SQL write access

A

available

43
Q

sql comments single

A

select shoe_id

- -,brand_id

44
Q

sql comments multiple

A 
select shoe_id
/*,brand_id       
,shoe_name
*/
from shoes_table
45
Q

editor for sql

A

notepad++

46
Q

sql lite, real datatype

A

A real data type is a data type used in a computer program to represent an approximation of a real number.

47
Q

Clauses

A

where, not, between, like, in, on, order by, group by

48
Q

why filter?

A
  • to get specific data
  • improve query operation
  • thereby improving performance
  • reduce the strain on the client application
  • FILTER BEFORE PULLING
49
Q

how to filter

A

where clause

50
Q

where clause operators

A 
=
<> 
<
>=
<=
BETWEEN
IS NULL
51
Q

where equal clause

A 
select colname1,
colname2,
colname3
from tablename
where colname1 = 'Tofu';

*the string condition is enclosed in single quotes

52
Q

string representations in sql query

A

full query enclosed in 1. ‘'’sql query’’’
2. “"”sql query”””

where condition enclosed in

  1. ’’ or
  2. ””
53
Q

> =
<=

  • applicable on only numbers
  • we can also keep other columns based on the filter applied
A 
select colname1,
colname2,
colname3
from tablename
where colname2 >= 29;
54
Q

give me everything except “Tofu”

A 
select colname1,
colname2,
colname3
from tablename
where colname1 <> 'Tofu';
55
Q

filter for a ranges of values

A 
select colname1,
colname2,
colname3
from tablename
where colname2 BETWEEN 15 AND 80
56
Q

IS NULL

A 
select colname1,
colname2,
colname3
from tablename
where colname1 IS NULL
57
Q

Advanced filtering

A

IN, OR, NOT

58
Q

IN operator

A 
select colname1,
colname2,
colname3
from tablename
where colname2 IN (9,10,11) ;
59
Q

OR operator

A 
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
Q

Cons of OR

A

ordering matters

61
Q

Pros of IN

A

no ordering
executes faster
can add another select to it as sub query

62
Q

Or and AND

What is important to have??

A

Important: Have paranethesis

select colname1,
colname2,
colname3
from tablename
where (colname1 = 'Tofu' OR 'Konbu'),
AND colname2 = 12;
63
Q

Not operator

A 
select colname1,
colname2,
colname3
from tablename
where NOT colname3 = 'London'
AND colname3 = 'Seattle';
64
Q

wildcards

A

Powerful pattern matching

*Like operator: can only be used with text data

65
Q

how to use wildcards

A

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
Q

can we use wildcard for null values

A

wildcards will not match null values

67
Q

underscore wildcard

A

where size LIKE ‘_pizza’

output: spizza
mpizza

68
Q

wildcards format/operator varies with database system

A

True

69
Q

bracket wildcard

A

some RDS use this

70
Q

cons of wildcards

A
  1. runs longer time
  2. format is different for different rds
  3. need to be careful while string matching implementation
71
Q

why sort data

A
  1. ordering can help to interpret the data easily

2. Helpful to easy retrieve the data

72
Q

rules order by

A
  1. must always be the last clause in a select statement
73
Q

sorting by positions

A

ORDER BY 2,3

2 means second column
3 means third column

74
Q

sort direction

A

DESC
ASC

can only be applied to column names

75
Q

multiplication example

A 
select
Product Id,
UintsOnOrder
,UnitPrice
,UnitsONOrder * UnitPrice AS Total_order_Cost 
FROM products
76
Q

order of operators

A 
parenthesis
exponents
multiplication
division'addition
subtraction
77
Q

combining math operator

A 
select
Product Id,
UintsOnOrder
,UnitPrice
,(UnitsOnOrder - Discount)/Quantity AS Total_Cost 
FROM products
78
Q

Aggregate function

A
  1. Provides various ways to summarize data
  2. used to summarize
  3. find highest and lowest values
  4. find total no of rows
79
Q

Agg functions

A 
AVG()
COUNT()
MIN()
MAX()
SUM()
80
Q

Average function

A 
SELECT AVG(Unitprice) AS avg_price
FROM products
81
Q

count function

count(*)

A

counts all the rows in a table containing values or NULL values

select count (*) AS
total
from tablename;

82
Q

count function

count(column)

A

counts all the rows in a specific column ignoring NULL values

select count(Colname) As
total
from tablename

83
Q

Max and MIN func

A 
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
Q

SUM

A 
SELECT SUM(Unitprice) AS total_price
FROM products
SELECT SUM(Unitprice*UnitsInStock) AS total_price
FROM products
85
Q

Distinct function

A
  • Distinct col names
  • cannot use distinct with count(*)
select count(distinct CustomerId)
FROM customers
86
Q

Grouping data

A
  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
Q

Grouping eg

A 
Select
Region
,COUNT(CustomerID) AS  total_customers
FROM Customers
GROUP BY Region
88
Q

Clause filtering for groups

points to note

A
  1. WHERE does not work for groups
  2. WHERE filters on rows
  3. Instead use HAVING clause to filter for groups
89
Q

Filter for customers having more than 2 orders

A 
Select
CustomerID
,COUNT(*) As orders
From Orders
Group By CustomerID
HAVING COUNT (*) >= 2;
90
Q

WHERE vs HAVING

A
  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
Q

Order by with

Group by

A

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;

Forte (11 decks)

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