This is yet another thread for technical reviews… I remember having some problems dealing with my data management courses. Now I think back what’s missing was an understanding of the relations and structure of databases. This weakness was reflected later in unfamiliarity with data manipulation and SQL queries. Worth practicing…
Note this is also related to knowledge under the label of “database” in computer science? Probably worth going over the LeetCode exercises…
Relational databases relational database is a database that organizes information into one or more tables.
A table is a collection of data organized into rows and columns. Tables are sometimes referred to as relations.
- a SQL statement always ends with semicolon ;
Create a table
CREATE TABLE table_name( column_1 data_type, column_2 data_type, column_3 data_type );
Insert a row. Note: unlike python sql does not take row break seriously. Statements end with “;”
insert into celebs (id, name, age) values (1, 'Justin Biber', 21);
Select from tables using column names. * is a special wildcard character that we have been using. It allows you to select every column in a table without having to name each one individually.
select * from celebs;
Update table. update updates the value of existing records.
update celebs set age = 22 where id = 1; select * from celebs;
Add column to a table
alter table celebs add column twitter_handle text;
select * from table_sample as s;
Select from two tables using LEFT JOIN. Note in the FROM clause there is only table 1!
SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person LEFT JOIN Address ON Person.PersonID = Address.PersonID
# Write your MySQL query statement below SELECT p.FirstName, p.LastName, a.City, a.State FROM Person AS p LEFT JOIN Address AS a ON p.PersonID = a.PersonID
select distinct genre
SELECT DISTINCT genre FROM movies;
select second highest salary. Note alising column name in “select” here!
SELECT MAX(Salary) AS SecondHighestSalary FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);
Select nth highest salary – this one is tricky!
Note the use of keywork “limit” in MySQL
If do not use “limit”, need to learn algorithms and databases… Resources here. Also Stanford course. Algorithms + databases two full courses will take (12+12)*10 = 240 hrs = 20 full days to learn. Need to somehow find that time in summer…
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT BEGIN DECLARE M INT; SET M = N-1; RETURN ( # Write your MySQL query statement below. SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1 ); END
2017 Apr 30
Installing and initializing MySQL on mac
- install using .dmg
- create user using directory utility in here for Mac 10.12 /System/Library/CoreServices/Applications command line: open ‘Directory Utility.app’
- This questions changes the PATH to mysql
- Use this command to access mysql ‘sudo mysql -u root -p’ — enter password that were gave to you when installing mysql through .dmg
- This question solves the reset password problem
- Accessing mysql databases
- This command line prompt seems to identify my host ‘ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘*****’;
Use Pandas to connect to mysql: For a client program to be able to connect to the MySQL server, it must use the proper connection parameters, such as the name of the host where the server is running and the user name and password of your MySQL account. Each connection parameter has a default value, but you can override them as necessary using program options specified either on the command line or in an option file.
Next step: connect using SQLAlchemy engine… What is that?
Nov 23, 2017
SQL Join: combine rows from two or more tables based on a related column between them