Introduction To SQL Programming

Part-3

Functions

MySQL has several built in functions which enable them to do much more than just storing and retrieving data . A function is simply a code snippet that performs a pre-defined task. Covering all the functions available in SQL is outside the scope of this course. You can take a look at them at this blogpost. We will be covering the use of some of some functions through the medium of a problem statement.

create database movies;
create table newmovie(id integer not null, title varchar(100),rating integer default 0,category varchar(100),grossrevenue_inmillion integer,check(rating<=5));
insert into newmovie(id,title,rating,category,grossrevenue_inmillion) values (1,"Tenet",4,"Thriller",1000);
insert into newmovie(id,title,rating,category,grossrevenue_inmillion) values (2,"TheRing",3,"Horror",1500);
insert into newmovie(id,title,rating,category,grossrevenue_inmillion) values (3,"Ford_vs_Ferrari",5,"Biopic",900);
insert into newmovie(id,title,rating,category,grossrevenue_inmillion) values (4,"RalphBreaksTheInternet",5,"Thriller",1000);
insert into newmovie(id,title,rating,category,grossrevenue_inmillion) values (5,"Prometheus2",4,"Horror",1000);
SELECT `id`,`title`, UPPER(`category`),rating,grossrevenue_inmillion FROM newmovie;
SELECT "The movie with the highest reveniue is:";
SELECT title,MAX(grossrevenue_inmillion) from newmovie;
SELECT "And the movie with the lowest reveniue is:";
SELECT title,MIN(grossrevenue_inmillion) from newmovie;
SELECT "The average rating of the movies in the bundle is:";
SELECT avg(rating) from newmovie;

Problem Statement 1:

Create a table of movies which consists of the following parameters: Name, Release Year, IMDB Score, Category(should be capitalized),Gross revenue. Further, display the movie having the highest and lowest revenue and the average rating of the movies.

Problem Statement 2:

Verify the trigonometric property: sin2 (x)+ sin2 =1 on SQL by using any random values.

create database trig;
create table lassooftruth(id integer not null, x integer default 0);
insert into lassooftruth(id,x) values (1,76);
insert into lassooftruth(id,x) values (2,45);
insert into lassooftruth(id,x) values (3,198);
insert into lassooftruth(id,x) values (4,65);
SELECT "The average value of the expression we get after inserting the above values in the identity is:";
SELECT AVG (POWER(SIN(x),2)+ POWER(COS(x),2)) from lassooftruth;
SELECT "Hence proved";

SQL Like Operator

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It is similar to regex in R programming. To demonstrate the usage of the like operator, let us create a table of players in a team and find out the names of the players whose name (surname) starts with “P”.

create database barca;
create table team(jerseynumber integer not null, name varchar(100));
insert into team(jerseynumber,name) values (3,"Pique");
insert into team(jerseynumber,name) values (7,"Griezman");
insert into team(jerseynumber,name) values (18,"Alba");
insert into team(jerseynumber,name) values (8,"Pjanic");
insert into team(jerseynumber,name) values (12,"Puig");
insert into team(jerseynumber,name) values (16,"Pedri");
insert into team(jerseynumber,name) values (10,"Messi");
SELECT * from team where name like 'p%';
Like OperatorDescription
WHERE CustomerName LIKE ‘%a’Finds any values that end with “a”
WHERE CustomerName LIKE ‘%or%’Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%’Finds any values that start with “a” and are at least 2 characters in length
WHERE CustomerName LIKE ‘a__%’Finds any values that start with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’Finds any values that start with “a” and ends with “o”

You can refer to the table above to customize your query.