Data : Data can be defined as the collection of numbers or measures or facts which is raw and unorganized and can be used for analysis.
Database : Database can be defined as the organized collection of structured data and the data is stored in the form of rows and columns which can also be called as tables. Databases helps in storage and manipulation of data.
Data Warehouse: Data warehouse is a data management system which have structured data that is preprocessed and acts as a central repository for analysis. We perform ETL in Data Warehouse.
Data Lake: Data Lake stores unstructured or raw data. Data can be stored in data lake and the process can be applied later on. We perform ELT in data Lake.
Data Mart: Data Mart can be considered as the subsets of data warehouse, as the data in the data marts are stored in the form of subsets to the data. For example one data mart contains all the sales data and the other have all the marketing team data etc.,
Data Modelling : It is creating schemas on the database . star, snowflake schemas. These popular data model example arranges the data into tables. The tables have columns and rows, each cataloging an attribute present in the entity. It makes relationships between data points easy to identify.
Star schema stores redundant data in dimension tables, while snowflake schema fully normalizes dimension tables and avoids data redundancy.
RDBMS : Relational database Management System stores and provides access to data points that are related to one other. In RDBMS data is stored in the form of tables.
Tables : The data is stored in the form of tables with rows and columns. Rows are called as records and columns are called as fields.
SQL : Structured Query Language is a programming language that is used to manipulate the data that is stored in different databases. Different commands are used to manipulate the data in the databases. Below are the commands we can use.
1. DDL: Data Definition Language. Create , Alter, Drop, Truncate.
2. DML: Data Manipulation Language. Insert, Select, Delete, Update.
3. DCL: Data Control Language. Grant, Revoke.
4. TCL: Transaction control Language. commit, Rollback, Savepoint.
Difference between delete, drop and truncate : Delete : It deletes either one row or all rows from the table. WHERE condition can also be used. ROLLBACK can be performed.
Truncate : It deletes all the rows in a table and where condition cannot be used. ROLLBACK can not be performed.
Drop : It deletes the whole table and even structure of the table do not exists.
Alias : Alias is used for creating temporary names for tables and columns where ‘AS’ keyword is used.
Select name as customer_name from customers;
Creating and deleting database in SQL :
Create Database Customerdata;
Use Customerdata;
Drop Database if exists Customerdata;
Creating and deleting tables in SQL :
Create table customers( name varchar() not null, age int, address varchar());
Drop table customers();
Above commands can be used to select or update or alter or insert the data.
select name from customers;
Insert into customers values (‘Ron’, 19, ‘Brooklyn’);
Update customers set age=20 where name=’Ron’;
Delete from customers where name=’Ron’;
Clauses used in SQL:
Where Clause : Where clause acts as a conditional statement. And it always comes before Group By clause.
select name, age, address from customers where age = ‘18’;
Distinct Clause : By using Distinct clause only unique records are selected.
Group By Clause: Group by clause is used to group the identical data into groups and it is followed by select statement and order by follows after group by.
Select name, age, address from customers group by address;
Having Clause : Having clause also acts a conditional statement , but it comes after Group By clause and is applied on only aggregated functions.
select distinct class from Students group by class having count(student)>=5 ;
Order By clause: Order By clause make the records to be in either descending order or ascending order.
Select name, age, address from customers group by address order by age ;
Operators used in SQL :
Comparison operators : =(equal to), > (greater than), <(less than), ≥(greater than equal to), ≤(less tham or equal to), <>(not equal to).
Logical operators : ALL, AND, BETWEEN, ANY, LIKE, NOT, OR, IN, SOME, EXISTS.
Arithmetic operators : +(addition), -(substract), *,(Multiply) /(Divide), %(Modulo).
Bitwise operators : &(bitwise AND), |(bitwise OR), ^(bitwise exclusive OR).
Select * from customers where name=’Ron’ and age=18;
Select name from customers where age=19 or address =’Brooklyn’;
Select * from customers where age between 20 and 30;
Order for writing sql statements is as follows :
Select, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
In further blogs joins , keys and subqueries topics will be explained.
