Skip to content

sql.md

Latest commit

 

History

History
71 lines (47 loc) · 2.11 KB

sql.md

File metadata and controls

71 lines (47 loc) · 2.11 KB

SQL

1. Install and get familiar with PostgreSQL

Follow the instructions given in this article https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart

2. Practice SQL

2a. SQLBolt

Complete SQLBolt 3 times.

2b. PG Exercises

Complete PostgreSQL Exercises 3 times. (Recursive Queries are optional)

3. Data Project - SQL Version

Solve the IPL data problem, but this time with SQL, using PostgreSQL database.

1. Create DB, permissions etc.

Write a SQL script that creates a new user, and database. Make the user the owner of the db.

2. Clean up script

Write another SQL script that cleans up the user, and database created in the previous step.

3. Load CSV

Write a SQL script that loads CSV data into a table.

4. Solve the IPL problems

In a SQL file write queries that will solve the problems of the IPL data set, but this time with SQL. You can copy paste the queries in a .sql file and submit.

  1. Number of matches played per year of all the years in IPL.
  2. Number of matches won of all teams over all the years of IPL.
  3. For the year 2016 get the extra runs conceded per team.
  4. For the year 2015 get the top economical bowlers.

Note: the result in this case is not a graph but SQL Result Set.

Submission

As with previous projects ...

  1. Create a repository - data-project-database.
  2. All the code for this assignment should be in that repo.
  3. Supporting files like .gitignore, README.md should also be included.
  4. The README should have all the info for your mentor to run the scripts.

4. SQL Concepts - Technical Paper

Write a technical paper in detail in markdown format

Some concepts to understand for databases:

  • ACID
  • CAP Theorem
  • Joins
  • Aggregations, Filters in queries
  • Normalization
  • Indexes
  • Transactions
  • Locking mechanism
  • Database Isolation Levels
  • Triggers

5. JDBC

Learn about JDBC.

Data Project - JDBC

Solve the problems using JDBC with PostgreSQL.