mserobabina/sql

Creating a set of data and using several of the basic DML forms available in PostgreSQL.

Queries in PostgresSQL

DDL was used to create tables, update the fields in an existing table. A set of commands were used to manipulate data stored in database.

Data Engineering

• Using the information to create a table schema for each of the six CSV files
• Specifing data types, primary keys, foreign keys, and other constraints
• Import each CSV file into the corresponding SQL table

Data Analysis

• Listing the following details of each employee: employee number, last name, first name, gender, and salary
• Listing employees who were hired in 1986
• Listing the manager of each department with the following information: department number, department name, the manager's employee number, last name, first name, and start and end employment dates
• Listing the department of each employee with the following information: employee number, last name, first name, and department name
• Listing all employees whose first name is "Hercules" and last names begin with "B"
• Listing all employees in the Sales department, including their employee number, last name, first name, and department name
• Listing all employees in the Sales and Development departments, including their employee number, last name, first name, and department name
• In descending order, listing the frequency count of employee last names, i.e., how many employees share each last name

Queries Used

Data Definition Language Statements:

• CREATE TABLE
• ALTER
• DROP
• CREATE VIEW
• DROP VIEW

Data Manipulation Language Statements:

• SELECT
• APPEND
• DELET
• UPDATE
• JOIN

Aggregate Functions:

• SUM
• COUNT
• GROUP BY
• WHERE

Data Modeling

Inspecting the CSVs and sketching out an ERD of the tables