Scribd
Upload
9 views

Intro To Database and SQL

Uploaded by

Divyansh Jain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
9 views

Intro To Database and SQL

Uploaded by

Divyansh Jain
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL - 01 - Intro to Database & SQL

Wednesday, August 10, 2022 9:05 PM

• Always first ask for ERD diagram


• It can also be extracted from MySQL Workbench by using "reverse Engineering" option

• use db; select * from table1; ------------> select * from db.table1;


• NULLs are empty spaces are different, NULLs don’t occupy memory

product_id int(11) means an we can store an int of max length 11

CHAR(size) is fixed length of 10, nothing more nothing less, it is good when you know the column will be exactly of that length, good for enforcing
length, like PAN card, it'll throw error when it's not
VARCHAR(size) is variable character that can have strings of length <= size
char gives slight speed advantage over varchar but varchar has memory advantages, varchar(60) doesn't store 60 bytes at the start, if the data stored in
only 16, then it'll store only that much, hence it's memory efficient

In ERD, Entity is like a table

• A foreign key in 1 table (table 2) refers to a column which is a PK of another table (table 1), it can have only those values which are present in the PK of
another table (table 1), it is used to ascertain that the values in the FK column of table 2 can contain no other values except the values present in PK of
table 1
• A FK can contain duplicate entries
• 1 table can have multiple FKs, each FK referring to one PK column of one table
• Super key is a logical combination of columns that can be used to uniquely identify a row (in case PK is not present), it's not like a programmatic
construct, just a logical one
• Candidate key is the min number of columns which when used as a combination can be used to uniquely identify a row (in case PK is not present), it's
not like a programmatic construct, just a logical one
• PK and FK can actually be implemented in DB tables while Super key and candidate key are just logical constructs to help us in understanding
• PK ⊆ Candidate key ⊆ Super key
• ERD relations:

• DDL changes the schema of table


• ALTER changes the column type, size, name, adds column, delete column
• DROP deletes data as well as schema, TRUNCATE deletes only the data and keeps the schema

• Always prefer SELECT colNames instead of SELECT *


• SELECT * can be used for dev/testing but not in production

ALIAS:

This can't be done as CostToCust is a temp output generated in this statement


If you see here, we're saying to select CostToCust from customer_purchases and since this column is not there in customer_purchases
it throws an error
We can use this if in an outer query if current query is an inner query

Q. If there are multiple columns having unique attributes in a table, how should we choose which column to keep as a PK?
I mean, apart from uniqueness, are there other criteria so that column is preferred as a PK over possible candidates?
=> Suppose I've to decide which column in table T1 to keep as PK, suppose I've defined a foreign key in table T2 then it must refer to a PK in table T1, now if
there are multiple choices of PKs in T1, then I should choose that column which is also present in T2 so that it can be made foreign key in T2

ACID properties

https://www.geeksforgeeks.org/acid-properties-in-dbms/

ACID properties have to be followed in RDBMS but that's not mandatory in NoSQL
RDBMS are preferred for OLTP (Online Transaction processing)
Conisdering the volume of data generated right now, we don't use RDBMS for OLAP (Online Analytical processing), we use big data technologies for this

OLAP systems can't achieve ACID properties because of the way they're designed (distributed systems)
NoSQL are a subset of OLAP systems
Some of these like MongoDB can satisfy ACID properties but they lack in availability, we'll see later on in details about this

Dimension and Fact table

You might also like