Head First SQL By Lynn Beighley

Today i was reading Head First SQL: Your Brain on SQL By Lynn Beighley.

Below are the key points i wanted to share from this book.

1) A database is a container that holds tables and other SQL structures related to those tables.

2) Single Quotes are special characters. When we are trying to insert a varchar,char and blob containing an apostrophe,we must indicate to your RDBMS that isn't meant for end of text but rather part of text.
One way is to add a backslash in front of the single queue.
For Example: insert into demo values(9,'\'')

3) We can use NOT with BETWEEN and LIKE as you can with IN.NOT goes right after where in your statement.
Example: SELECT drink_name FROM drink_info d where NOT carbs between 3 AND 5

b) When we use NOT with AND or OR it goes right after AND or OR.
Example: SELECT drink_name FROM drink_info d where NOT carbs between 3 AND (NOT carbs like 5)

How ever below statements will give exactly same result.

SELECT drink_name FROM drink_info d where carbs NOT IN (8.40,3.20)

SELECT drink_name FROM drink_info d where NOT carbs IN (8.40,3.20)

4) A column with atomic data cannot have several values of the same type of data in that column.

5) A table with atomic data cannot have multiple columns with the same type of data.

6) Making your data atomic is the first step in creating a NORMAL table.

7) The case expression combines all the update statements by checking an existing column's value against a condition.If it meets the condition,the new column is filled with a specified value.
Example:
update projekts set descriptionofproj=
case
when number=1 then 'prashant'
when number=2 then 'sneha'
else 'newvalue'
END;

8) A description of the data(the columns and tables) in your database,along with any other related objects and the way they all connect is known as schema.

9) For Many-to-Many we may need a junction table which holds a key from each table.

10) A table is in 1NF (First Normal Form) if
A) Columns contain only atomic values.
B) No repeating groups of data.

11) If changing any non-key columns might cause any of the other columns to change,you have a transitive dependency.

12) Second Normal Form (2NF) focuses on how the primary key in the table relates to the data in it.A table in 1NF is also in 2NF if all the columns in the table are part of the primary key OR It has a single column primary key.A table is in Second Normal Form (2NF) if it has an artificial primary key and no composite primary key.

13) A table is in Third Normal Form(3NF) if it is in 2NF and there are no transitive dependencies.

14) We can create,insert into table AT a time by using "as" keyword like below query.Hence it will try to create a table dummy with a columnName number and values popuated from projekts table.
Example: create table dummy AS select number from projekts

15) Table Alias are also called correlation names.
Example: select * from dummy d group by number

16) Cross Joins returns every row from one table crossed with every row from the second.
Example: select t.toy,b.boy from toys AS t CROSS JOIN boys as b;

17) An INNER JOIN is a CROSS JOIN with result rows removed by a condition in the query.Non-equijoin returns any rows that are not equal. Uses <> (not equal to) operator.

18) Natural joins works only if the column you're joining by has the same name in both tables.
Example: select boys.boy,toys.toy from boys NATURAL JOIN toys; This works until both boys and toys tables have both same column.

19) A subquery is a query that is wrapped within another query.It's also called an INNER query.

20) SQL Rules of Order
A) A subquery is always a single SELECT statement.
B) Subquery are always inside parenthesis.
C) Subquery donot get their semicolon.
D) SubQuery can show up in 4 places in a query
SELECT clause,SELECT COLUMN LIST as one of the columns,FROM clause,and in HAVING clause.

E) Sub queries can be used with INSERT,DELETE,SELECT,UPDATE.

21) If the subquery stands alone and doesn't reference anything from the outer query,it is a noncorrelated subquery.

22) Just like IN and NOT IN we can both use EXISTS and NOT EXISTS with your sub queries.

23) LEFT OUTER JOIN matches EVERY row in the LEFT table with a row from the right table.Outer join gives us a row whether there's a match with the other table or not.A NULL value in the results of the left outer join means that the right table has no values that correspond to the left table.

24) RIGHT OUTER JOIN evaluates the right table against the left table.

25) A self referencing foreign key is the primary key of the table used in that same table for another purpose.

26) A Self join allows you to query a single table as though there were two tables with exactly the same information in them.

27) Another way to get combined results from two or more tables called a UNION.
Example: select firstname from table1
UNION
select secondname from table2

28) SQL Rules of union are:
-> Number of columns in each select statement must match.You cannot select 2 columns from the first statement and one from the next.
-> We must have same expressions and aggregate functions in each SELECT statement.
-> Order of select statements does not count.
-> By default SQL suppresses duplicate values from the result of UNION.
-> Data types in the columns need to be same or be convertable to each other.
-> If you want duplicate data use UNION ALL.

29) We cannot use aggregates in where clause without a subquery.

30) One Diff between Inner Joins and SubQueries is Subqueries can be used in UPDATE,INSERT,DELETE.

Hope you enjoy reading this book.