Advanced Database Lecture 1 PDF

COMP-10279 Advanced Database Lecture 1 Introduction and SELECT  Agenda Course introduction SELECT SELECT syntax examples  Course Evaluation Assignment(s)(x4) 40% – Released every 2 weeks Midterm Exam 30% – 2 hours – Held the week befo...

COMP-10279 Advanced Database Lecture 1 Introduction and SELECT  Agenda Course introduction SELECT SELECT syntax examples  Course Evaluation Assignment(s)(x4) 40% – Released every 2 weeks Midterm Exam 30% – 2 hours – Held the week before study week Final exam 30% – 2 hours – Held during week 14  Course Tools-MySQL You can access MySQL from either one of the following two tools: – XAMPP – MySQL Workbench Both are available on MohawkApps You can download MySQL workbench for your personal use from here. You can download XAMPP from here.  Course Tools-MongoDB We will be primarily using MongoDB Community which is available on MohawkApps You can also download and install MongoDB community server here In week 11, we will also go over MongoDB Atlas that hosts MongoDB in the cloud and connect to the database from Node.  Why MySQL? Many of the world's largest and fastest-growing organizations including Facebook, Twitter, Booking.com, and Verizon use MySQL. MySQL is the second ranking RDBMS solution in the world and comes second to Oracle Runs on almost every platform – Linux, Unix, Windows, z/OS, …  SQL Even though we will be using MySQL, the concepts and much of the syntax are directly transferrable to other DBMSs Oracle, PostgreSQL, SQL Server, SQLite, and many others Databases and Connections Databases can be created with SQL scripts The Student Resources course page has scripts for all the database we will be using throughout the semester.  CRUD Consider the life of a record Creat INSER Once e T Read SELEC Regularly T Updat UPDAT Seldom e E Delet DELET Once, perhaps never e E  SELECT Without doubt, the most important and heavily used SQL statement Standard syntax for SQL statement: – SELECT column_to_select FROM table_to_select WHERE conditions; SELECT syntax can also get very complex – https://dev.mysql.com/doc/refman/8.4/en/selec t.html  SELECT All Columns or Specific Columns SELECT * FROM nursing_units; SELECT nursing_unit_id, specialty FROM nursing_units; SELECT * FROM world.city; SELECT Name,CountryCode, District FROM world.city;  Whitespace SQL ignores whitespace Convention is to place each clause on its own line SELECT * FROM world.city; SELECT * FROM world.city;  DISTINCT Removes Duplicates SELECT nursing_unit_id FROM admissions; SELECT DISTINCT nursing_unit_id FROM admissions; SELECT CountryCode FROM world.city; SELECT DISTINCT CountryCode FROM world.city; Comparison Operators < less than > greater than = greater than or equal to = equal to not equal to  WHERE Clause Limits Results SELECT nursing_unit_id, specialty FROM nursing_units WHERE beds < 10; select film_id, title, release_year from sakila.film where language_id = 1;  LIKE Clause to search for a pattern Two wildcards used with LIKE o The percent sign (%) represents zero, one, or multiple characters o The underscore sign (_) represents one, single character SELECT * FROM patients WHERE last_name LIKE 'Smi%’; SELECT * FROM sakila.actor where first_name like "%Cam%"; SELECT * FROM chdb.patients where province_id like "O_";  Case Sensitivity SQL is not case sensitive, search string for queries are also case insensitive Lower case and singular names for tables, e.g., address, customer, etc. For column names, use lower case, short names. Words can be separated by underscore, e.g., start_date, end_date.  AND, OR and NOT SELECT * FROM patients WHERE patient_height < 100 AND patient_weight > 30; SELECT * FROM patients WHERE patient_height < 50 OR patient_weight < 10; SELECT * FROM chdb.patients where city = "Toronto" and NOT gender = "M";  IN, NOT IN, BETWEEN SELECT * FROM patients WHERE allergies IN ('Lactose', 'Gluten', 'Aspirin’); SELECT * FROM chdb.patients WHERE allergies NOT IN ('Penicillin','NKA','ASA'); SELECT * FROM patients WHERE patient_height BETWEEN 50 AND 55; SELECT * FROM patients WHERE province_id BETWEEN 'AB' AND 'MB'; SELECT * FROM world.country where Region IN ("Caribbean", "South America", "Middle East"); ---You will notice in the query above that even if you use lower case for the regions, the search will work because it is case insensitive