A QUICK OVERVIEW!! SQL

INTRODUCTION: SQL known as a structured query language , is a standardize and powerful programming language that is designed for storage and management. It allows us to create , parse, and manipulate data fast and easy.

-When a user wants to get some information from a database file, he can issue a query.

• A query is a user–request to retrieve data or information with a certain condition.
  – SQL is a query language that allows user to specify the conditions. (instead of algorithms)

WHAT IS DATABASE?

database is a collection of structured and presist data.

WHAT IS DATABASE MANAGEMENT SYSTEM(DBMS)?

• DBMS is a software system that supports creation,population and querying of a database.

GOOD DATABASE:

• divides your information into subject-based table to reduce redundant data.
• provides access with the information it requires to join the information in the tables together as needed.
• help, support and ensure accuracy of the data.
• accommodates data processing and reporting needs.

STRUCTURE:

structure of SQL

SQL STATEMENTS TYPES:

• Data Definition Lang(DDL) for defining relations, views, integrity constraints
  • Data Manipulation Lang(DML) for updating and querying
  • Database Control Lang(DCL) for defining access rights, concurrency
  control, etc.

SQL IS CASE-SENSITIVE OR SPELLING-SENSITIVE?

• SQL is case-sensitive. we can’t write more than ONE SQL Statement in one
  window (in Access)
• Any function –
  – A function looks like -> funcName()
  – It will always return something
  – Some functions accept parameters or arguments. Eg.
  Round(8.89, 1) -> 8.9

NEED TO KNOW SOME STEPS BEFORE WRITING ANY SQL SCRIPT:

• determine the purpose
• find and organize information
• then divide them into tables
• set up table relationship
• specify primary key
• turn items into columns
• refine your design
• apply normalization rules

DESIGN OF DATABASE:

for design of SQL database we need to familiar with

1. primary key: the unique value of a table is known as primary key
2. foreign key: it referces to the primary key of another table
3. composite primary key: together with the primary key and foreign key

SCRIPTS FOR CREATING A DATABASE:

/*This is how you write comments*/

/*The following statement will create the customers table with columns and constraint (restrictions on the columns)*/

CREATE TABLE Customers

(CustID INT,

CustName VarChar(100),

CustCity VarChar(100),

CustNetWorth INT,

Constraint cust_pk PRIMARY KEY(CustID),

Constraint cust_chk Check(CustNetWorth > 0) );

/*Describe command will describe the design of a table.*/

Describe customers;

CREATE TABLE Orders

(OID INT,

OrderDate date,

OrderShipDate Date,

CID INT,

Constraint order_pk PRIMARY KEY(OID),

Constraint order_fk FOREIGN KEY(cID) REFERENCES Customers(custID) );

CREATING DATABASE:

DATA>>>> INFORMATION>>KNOWLEDGE

BASE>>>>STORE THE INFORMATION

so what do you think any database look like? It is interesting to design any database when you will identify their relationship ,how they are connected to each other.

• here is a example of stationary database which is designed in SQL workbench

stationary database

so we can see a table can be connected with another table by-

• one to many - here we can see a customer with one custId can give many orders(each order will have different order Id). so they are connected by one to many relationship.
• many to many-suppliers and products they are connected with many to many relationship . and for that we need to create one more table called JUNCTION table(here is supplier product). order and product is also connected with many to many relationship.
• one to one- is this database there is no one to one connected table. as a example we can say employee and salesman can be connected by one to one relationship.

so these are the basics of SQL statement.

NOW, do you know why SQL is need in data science??

think!! without database we are in the world of data science and that is completely meaningless!

SQL is needed for data scientists to get the data and to work with the data. SQL codes are more efficient yields easily reproducible scripts , and keeps you closer to the data . Learning own SQL can also give you a leg up on folks transitioning , often with limited database experience , into the data science industry.

HERE IS SOME SIMPLE QUESTIONS FOR YOU:

1. Is tableau is a database? — no, it is a reporting tool(data visualization)
2. Is excel is a database?-no, it creates report on data (visualization)
3. is SQL harder than python?- SQL is like a simple english language. the grammar is smaller, the amount of different concepts is smaller. so as a language sql is easy than python. But as a tool, SQL is more difficult than Python coding ,IMO.
4. What do you mean by table and field in SQL?- A table refers to a collection of data in an organised manner in form of rows and columns. A field refers to the number of columns in a table. for example: TABLE: studentinformation; FIELD: stu id, stu name;
5. What is a unique key?- unique key uniquely identifies a single row in the table, allows multiple values per table, allows null values.