- Sql Basics
- Sql Queries
- Sql Joins
What is SQL
- Structured Query Language, pronounced as “SQL”.
- Used for Creating, Accessing, Exploring, Cleaning, and Analyzing data
- SQL is a programming language for interacting and managing the databases
- SQL is relatively easy to learn
- Very few commands in whole language
- Intuitive code structure and easy syntax
What is a database
- Organized collection of data
- A collection of files storing related data or a coherent collection of data
- Database contains
- Schemas – Data structure
- Views – Virtual tables, it doesn’t contain any data and it is dynamically calculated
- A single database can contain multiple tables and queries
- Universities: Students database, registration, grades
- Sales: customers, products, purchases
- Manufacturing: production, inventory, orders, supply chain
- Human resources: employee records, salaries, tax deductions, Payroll database
- Amazon’s products database
- Banking Accounts database, all transactions
- Airlines: reservations, schedules
Tables, columns and records
- Tables contain homogeneous data records
- Contains columns and records
- Table is the combination of rows and columns, where each row is a record or a data value. Each column gives some specific information about the record.
- The table looks like the details of some products.
Database Management System
- If we have a database, i.e., the collection of tables or data inside it, we need a software to interact with Databases.
- We need to query the database to get a particular information about the database. Like if we have an employee database, how to get the info about an employee working at a particular location?
- To perform such operations and extract the information, we need a software to interact with the database.
- DBMS is a software, designed for the definition, creation, querying, update, and administration of databases
- All information in database cannot be accessed from the database, only privileged or authorized people can access some vital information. This is managed by the DBMS.
- A database created in a DBMS system may not be compatible with other DBMS software’s
- We can use SQL language in all DBMS
- We can also use JDBC and ODBC drivers for connecting to DBMS
- Relational Database Management System. A DBMS that is based on the relational model
- The tables have an identifier called primary key
- It is similar to DBMS but its a DBMS based on relational model. The tables in this will be somehow connected. There will be the primary key, and based on primary key, the tables are connected. Hence it is called as a relational database management system.
Famous RDBMS softwares are
- Microsoft SQL Server
- IBM DB2
DDL and DML
- SQL id used for two broad types of functions
- DDL – Data Definition Language
- Create database
- Create tables
- Access rights
- Manage Authentication
- DML – Data manipulation language
- Insert data
- Query the data
- Analyze the data
In Data Analysis course we focus on “Data Manipulation Language”.
LAB: Installing Database
- For writing SQL queries, we need MySQL workbench or MySQL command line client installed in our system.
- Right now we do not have a database. Before we write any queries, we have to make sure that there is a database that we can access. So lets us intall the database. This need not be done always. We can create database just once and work on it.
- Now open MySQL.
- Create a user in Mysql
- We have to create a new connection with a name let’s say, “local”. The username and password for local will be root. This is for our convenience, we can use any name for user and password.
- An sql file is shared, which will automatically create a database.
- Open the sql file in a new query tab and run it. The database “classicmodels” will be automatically created.
- In the Action Output window, under schemas we can see the database created.
- We will be writing queries with respect to this database.