SQL Review – Updating…

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…

Apr 23

Concepts 

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.

Properties

  1. a SQL statement always ends with semicolon ;

Sample code 

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;

No… CodeAcademy is too slow for this. Another recource here  a one-page guide. I should do this by working on problems.
table aliasing: like creating a pointer or something..

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

Using alias

# 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

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *