CRUD

Create:

Used to create database, table and to insert information in it.

syntax:

create database <database_name>;

create table <table_name> (

columns_name datatype constrains );

insert into table_name (columns) values (value1);

In insert into query you don't need to specify the columns.

syntax:

insert into film values(default, 'The Dark Knight', 'Batman fights the Joker', 2008, 1, NULL, 3,4.99,152, 19.99, 'PG-13', 'Trailer', default);

But issue is that you need to remember the order and datatype of all columns and you cannot skip any column and can change their order as to understand the values table need to map the value with respective columns and hence it will consider the definition and will put information in same order hence always giving column names and then value is preferred.

Drawbacks of using insert into command without column names in syntax:

Not good practice and make query prone to errors as columns are not specified and you need to remember order of columns to insert values into it.
As you can understand it made writing query tedious.
You need to specify values for all the columns and if count of columns are huge and most of them acquire default values or get autofill then it will cause an issue and inconvenience to write so many information in a query which is not maditory.

Read:

select - print
You may print constant data or data from table
Most used query

syntax Select:

to print constant value.

select constant_value;

To print whole table data:

select * from table_name;

* --> means record in a table.

To print specific columns from a table:

select col1, col2 from table_name;

Pseudo code: for select * from table_name;

table_name : [[],[],[],[]]

ans = [] - > Intermediate table for information extraction and to work with it without disturbing original table.

for row in table_name:

ans.add(row)

for row in ans:

print(row);

-- print student_id and full name of a student from student table.

select s_id, concat(first_name, last_name) as full_name

from school.students;

select 'name' as info from students; -> It will print the constant named name with respect to records present in a table named students. It is used when you need to generate data for all the records for reference as dummy data for testing. It will help you to check the records and load and time complexity to execute a command for stress testing.

Distinct keyword:

It helps to get unique values from the table. Combination of columns will define it's behaviour as it's work is to find the distinct records and to display it.

select distinct * from students; -> it will check entire record and then will display distinct entries.

select distinct first_name from students; -> it will check only unique column information and will display it.

Rules to use it:

It should be first keyword after select keyword in a query.
It can be applied on record or on a column or pair of columns or any number of columns as subset.
Filters out duplicates.

some more examples:

-- display all the years when movie released and entry is present in sakila database under film table.

select distinct release_year from sakila.film;

select * from sakila.film;

-- from films table get distinct pairs of release years and ratings.

use sakila;

select distinct release_year, rating from film;

Pseudo code:

film: [[],[],[],[]]

ans= []

for row in film:

ans.add(row)

filtered_ans = []

for row in ans:

filtered_ans.add(row[release_year], row[rating])

unique_ans = set(filtered_ans)

print(unique_ans)

Operations on select queries:

-- Get title and length of each movie in hours?

select title, length/60

from film;

-- Get length of movies in approx whole number?

select round(length/60) as duration

from film;

-- Get length of movies in approx two decimal number?

select round(length/60, 2) as duration

from film;

-- Given length and rental duration of movies, get the number of times we can watch it?

select

title,

length,

rental_duration,

round(rental_duration/(length/60)) as possible_time_watched_movie

from film;

-- round keyword will round the number to nearest whole number.

select round(8.8);

Inserting data from another table:

use school;

create table students_detail

(

s_id int primary key auto_increment,

first_name varchar(100) not null,

last_name varchar(100) not null,

psp int not null,

marks int not null

);

insert into students_detail

(first_name, last_name, psp, marks)

values

('Puneet ', 'Kumar Singh', 99, 100),

('Anu ' , 'Singh', 100, 100),

('Devansh ', 'Singh', 100, 100),

('Rupesh ', 'Khanna', 34, 12);

select * from students_detail;

create table student_info(

student_id int primary key auto_increment,

first_name varchar(100) not null,

last_name varchar(100) not null);

insert into student_info (first_name, last_name)

select first_name, last_name

from students_detail;

select * from student_info;

Where clause (similar to if condition in coding):

It help to filter out the data and display result matching to filter data criteria and help to sample the data and even help to understand the data and help to perform many operations on selective data rather than doing on entire table unintentionally.

-- display all records from film table where rating if pg-13

select * from film where rating = 'pg-13';

select count(*) from film where rating = 'pg-13';

Pseudo code:

table_name = [[],[],[],[],[]]

ans = []

for row in table_name:

if row.matches(condition in where clause)

ans.add(row)

for row in ans:

print(row)