Skip to content

Latest commit

 

History

History
225 lines (182 loc) · 5.97 KB

Concepts.md

File metadata and controls

225 lines (182 loc) · 5.97 KB

Fundamental Concepts

  • SQL is a standardized structured query language that helps to communicate with RDBMS.
  • A database is a collection of data (tables, views, etc) while DBMS is software that helps you manage information in the database.
  • DBMS simplifies your data collection, maintenance, storage and tracking. DBMS helps to organize and maintain your data.
  • SQL helps to define tables using columns and rows to find and update the values in specific cells.
  • Columns in relational database tables must have a unique name and homogeneous (same data type) values.
  • Row is a single record of data.
  • NULL value stands for a blank value (Not zero and no whitespace)
  • Database acts as a folder for multiple tables and views.

Keys

Keys Description
Primary Column that stores unique values and uniquely identifies each rows in a table.
Foreign Column that stores unique values and uniquely identifies each row of some another table (Primary Key of another table)
Natural Column that stores unique values that are already present in the data (Combination of two or more values)
Surrogate Column that stores unique values that are created instantly (Random autogenerated numbers)

Metadata

  • Provides the basic and relevant information about the data (size, datatypes, non-null, # rows, # columns)
  • Metadata function in SQL Server returns information about the database, database objects, files, groups, etc.

Operations

  1. Creating a table
  2. Inserting rows
  3. Deleting rows
  4. Deleting a table
  5. Drop existing table
  6. Changing Schema
  7. ID Column
  8. NULL value
  9. Ordering rows

SELECT

SELECT applicant_ID, applicant_name, position, stage_name, applicant_status
FROM applicants
WHERE applicant_status = "New" 
AND country IN ("India", "UK", "USA") 
AND (age BETWEEN 25 AND 30)
AND position LIKE "%Engineer%";
SELECT position, COUNT(applicant_ID) AS applicant_count, AVG(salary_expectation) AS AvgSalary
FROM Applicants
WHERE salary_expectations > 2000
GROUP BY 1
HAVING applicant_count > 10 
ORDER BY avg_salary ASC
LIMIT 3;
SELECT position, COUNT(applicant_ID) AS applicant_count
FROM db1.applicants app
LEFT JOIN db2.channels ch
ON app.applicant_ID = ch.ID
WHERE source IN ("Glassdoor")
GROUP BY position
ORDER BY applicant_count DESC

Creating a table

CREATE TABLE employee
(
  ID INT PRIMARY KEY,
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  age INT CHECK (age > 18),
  birthdate DATE,
  designation VARCHAR(25)
);

Inserting rows

-- Insert every row value:
INSERT INTO Employee
VALUES ('Kirankumar', 'Yadav', 'Data Scientist');

-- Insert only specific row values, the rest will be NULL :
INSERT INTO Employee (first_name, last_name)
VALUES ('Kisankumar', 'Yadav')

-- Insert a new row with NULL values :
INSERT INTO Employee
DEFAULT VALUES;

-- Insert a new row values from an existing table :
INSERT INTO Employee (first_name, last_name, designation)
SELECT first, last, role FROM training;

Deleting rows

DELETE FROM employee WHERE ID = 3

Deleting a table

DROP TABLE employee

Deleting existing table

  • Drop the table only if that table exists in a database.
  • No error will be visible if there is no such table in the database.
  • Normally if we just drop the table it will display an error that there is no such table in the database.
DROP TABLE IF EXISTS employee;

Changing Schema

  1. Adding columns in an existing table.
  2. Removing columns from an existing table.
  3. Adding or changing constraints of an existing table.
  4. Changing data type of an existing column.
CREATE TABLE employee
(
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  designation VARCHAR(25)
);

INSERT INTO employee
VALUES ('Kirankumar', 'Yadav', 'Data Scientist'),
       ('Suraj', 'MS', 'MLE'),
       ('Pavan', 'Kumar', 'Statistician');

SELECT * FROM employee;

Output

First_Name Last_Name Designation
Kirankumar Yadav Data Scientist
Suraj MS MLE
Pavan Kumar Stistician 
ALTER TABLE 
ADD department VARCHAR(25) DEFAULT 'Data Science';

SELECT * FROM employee;

Output

first_name last_name designation department
Kirankumar Yadav Data Scientist Data Science
Suraj MS MLE Data Science
Pavan Kumar Statistician Data Science

ID Column

  1. Column that holds a unique value for each row in a table.
  2. Typically ID columns are automatically populated or incrmented.
CREATE TABLE employee
(
  employee_ID INTEGER PRIMARY KEY
  first_name VARCHAR(25),
  last_name VARCHAR(25),
  designation VARCHAR(25)
);

INSERT INTO employee (first_name, last_name, designation)
VALUES ('Kirankumar', 'Yadav', 'Data Scientist'),
       ('Suraj', 'MS', 'MLE'),
       ('Pavan', 'Kumar', 'Statistician');

SELECT * FROM Employee;

Output

employee_ID first_name last_name designation
1 Kirankumar Yadav Data Scientist
2 Suraj MS MLE
3 Pavan Kumar Stistician

NULL value

-- Fetch only rows with a NULL value :
SELECT * FROM employee
WHERE designation IS NULL

-- Fetch only NON NULL rows :
SELECT * FROM employee
WHERE designation IS NOT NULL

Ordering rows

-- Order the rows in Ascending Order | ASC by default :
SELECT first_name, last_name, designation 
FROM employee
ORDER BY first_name;

-- Order the rows in Descending Order :
SELECT first_name, last_name, designation 
FROM employee
ORDER BY first_name DESC;

-- Order the rows based on multiple columns :
SELECT first_name, last_name, designation 
FROM employee
ORDER BY first_name ASC, last_name ASC, designation DESC;