SQL Lecture Notes PDF

INFO90002 Lecture 5 What is SQL A language that is used to work with relational database systems and supports CRUD (create, read, update, delete commands) SQL supports CRUD through commands CREATE, SELECT, INSERT, UPDATE, DELETE, DROP commands SQL Language Prov...

INFO90002 Lecture 5 What is SQL A language that is used to work with relational database systems and supports CRUD (create, read, update, delete commands) SQL supports CRUD through commands CREATE, SELECT, INSERT, UPDATE, DELETE, DROP commands SQL Language Provides capabilities in DDL, DML, DCL, and other commands How we use SQL In implementation of the database - Take the tables we design in physical design - Implement these tables in the database using create commands In use of the database - Use SELECT commands to read the data from the tables, link the tables together, etc. - Use ALTER, DROP commands to update the database - Use INSERT, UPDATE, DELETE commands to change data in the database We start with the command CREATE TABLE Bank HQ ( We have names of fields with ID, address and other details ID is integer type, and it auto increments which means database will automatically populate it starting from 1-3, giving an automated primary key. Things like address is stored in VARCHAR which represents variable type and means variable character type that says give up to () allocated CHAR is less flexible than VARCHAR but more efficient. The number it specifies is the exact amount of characters There is also a TEXT type for open ended pieces of text Inserting into tables The 1 doesn’t need to be there as database will look after itself and you don’t need to specify ID. It will auto increment. The first one, you could go straight to VALUES because there is 3 columns which corresponds to 3 values The second version is used when you want to put less items than columns, SQL will not know which columns they want to go in. you have to put prefix section where it explicitly shows column names of where you want to put data values If you skip ahead of its auto increments, it catches up and goes to the number after you specified NOT null specifies this value cannot be void DEFAULT can be the placeholder for ID Foreign Keys ON DELETE RESTRICT ON UPDATE CASCADES means if there is an account that points to a customer, you cant delete the customer. You can only delete a customer if there are no accounts pointing to it. This is to maintain consistency and integrity in the data You put another tables ID, and in the table you have to insert a valid ID from the place referenced SELECT SELECT * FROM customer, functions as selecting all columns from customer To specify specific columns, replace * with the column names With regards to adding WHERE statements, its also a filter that can say function to find only customers with last name smith  LIKE statement can be used to find anything that starts with Sm followed by % which represents everything after it After select, for specific columns, the ordering inserted in the commands will be the order on the table character behind % represents the ending character Wild card (%) just means substitute with anything with 0 or greater characters MySQL is case insensitive Aggregate functions Operate on the subset of values in a column of a relation table and return a single value These include COUNT, AVG, MIN, SUM, and MAX COUNT (column) will count null characters and will detect them as it counts how many records that “in” something COUNT (*) will just count all of them as it count how many records in total  This is to show that COUNT (column) may not equal COUNT(*) because that column might have some null values in them Other aggregate examples include COUNT and AVG Adding GROUP BY at the end, will break the table down into a group by for each  We can rename column type by using the AS clause Order by can have 2 or more tiers, which is shown in example as it first orders by ID and then for people with same ID we then order by last name LIMIT and OFFSET Offset will skip N types of records, OFFSET 3 skips first 3 rows Joining tables You can reference the same table more than once and in 2 different ways in a single query Sometimes you will explicitly represent a column by having table.column name Joining tables together Using SELECT * FROM Customer, Account ; will join the tables through cross multiplication (every possible combination between different rows) This cartesian product is of little use INNER JOIN  This method shows the clarification It is essentially matching specific columns from 2 different tables and joining them Commonly used for ID as the items in the columns also have to match. This therefore is usually joined by ID but can also be done for other things depending on business needs Natural join Avoid old style joins There are also different types of joins Outer join Outer join Joins the tables over keys Can be left or right Includes records that don’t match the join from the other table Set operations Union Combine the results of two queries (or tables) into a single result set The number and data types of the columns selected by each component query must be the same, but the column lengths can be different Intersect Shows only rows that are common the queries (or tables)  If we are going to use union then both results set bought together have to have same sets of columns UNION ALL will be show all instances of something for example if connie is a tutor and a student, then it will show it twice whereas union will just show one instance of them SELECT with literals For every record that results from this SELECT statement, you can actually give it a value People on far left has no one bossing them People on right side of inner join are bosses of someone People on far right is a boss of no one To sort them into a group, the above query could be used LEFT OUTER JOIN As compared to INNER JOIN it will also include Alice on the list It means that if there is anybody in an emp (emp, left) instance that doesn’t match with anything on the right (boss instance), still include them once Right outer join pretty much means if something on the right doesn’t match with something on the left of the join, still show it FULL outer join includes both RIGHT and LEFT, to do this in SQL you just get the UNION of left and right outer joins More on INSERT Update statement