SQL/MySQL Database Developer: Zero to Expert||Hindi|Urdu||

CREATE TABLE dog4

(   

    name VARCHAR(100) NOT NULL DEFAULT 'DEFAULT_dog4',

    age INT NOT NULL DEFAULT 99,

  );

SECTION4_Exercises

CREATE TABLE dog4

(   

    name VARCHAR(100) NOT NULL DEFAULT 'DEFAULT_dog4',

    age INT NOT NULL DEFAULT 99,

  );

CREATE TABLE dog7_key

(   

    dog_id INT NOT NULL AUTO_INCREMENT

    ,name VARCHAR(100)

    ,age INT

    ,PRIMARY KEY (dog_id)

  );

INSERT INTO dog6_key(dog_id, name, age) VALUES(1,'D6',22);

CREATE TABLE class (

    id INT AUTO_INCREMENT NOT NULL,

    first_name VARCHAR(255) NOT NULL,

    last_name VARCHAR(255) NOT NULL,

    middle_name VARCHAR(255),

    age INT NOT NULL,

    Marks INT NOT NULL DEFAULT 0,

    PRIMARY KEY(id)

);

ls

SOURCE books.sql;

CREATE TABLE books

(

book_id INT NOT NULL AUTO_INCREMENT,

title VARCHAR(100),

author_fname VARCHAR(100),

author_lname VARCHAR(100),

released_year INT,

stock_quantity INT,

pages INT,

PRIMARY KEY(book_id)

);

INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)

VALUES

('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),

('Norse Mythology', 'Neil', 'Gaiman',2016, 43, 304),

('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),

('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),

('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),

('The Circle', 'Dave', 'Eggers', 2013, 26, 504),

('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),

('Just Kids', 'Patti', 'Smith', 2010, 55, 304),

('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),

('Coraline', 'Neil', 'Gaiman', 2003, 100, 208),

('What We Talk About When We Talk About Love: Stories', 'Raymond', 'Carver', 1981, 23, 176),

("Where I'm Calling From: Selected Stories", 'Raymond', 'Carver', 1989, 12, 526),

('White Noise', 'Don', 'DeLillo', 1985, 49, 320),

('Cannery Row', 'John', 'Steinbeck', 1945, 95, 181),

('Oblivion: Stories', 'David', 'Foster Wallace', 2004, 172, 329),

('Consider the Lobster', 'David', 'Foster Wallace', 2005, 92, 343);

SELECT CONCAT(author_fname,' XX ',author_lname,'XX ',title) FROM books;

SELECT CONCAT_WS(' XX ',author_fname,author_lname,title) FROM books;

SELECT

SUBSTRING(title,1,5)

FROM books;

SELECT

REPLACE(title, '  ' , 'Space')

FROM books;

MySQL String Functions

Function Description

ASCII Returns the ASCII value for the specific character

CHAR_LENGTH Returns the length of a string (in characters)

CHARACTER_LENGTH Returns the length of a string (in characters)

CONCAT Adds two or more expressions together

CONCAT_WS Adds two or more expressions together with a separator

FIELD Returns the index position of a value in a list of values

FIND_IN_SET Returns the position of a string within a list of strings

FORMAT Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places

INSERT Inserts a string within a string at the specified position and for a certain number of characters

INSTR Returns the position of the first occurrence of a string in another string

LCASE Converts a string to lower-case

LEFT Extracts a number of characters from a string (starting from left)

LENGTH Returns the length of a string (in bytes)

LOCATE Returns the position of the first occurrence of a substring in a string

LOWER Converts a string to lower-case

LPAD Left-pads a string with another string, to a certain length

LTRIM Removes leading spaces from a string

MID Extracts a substring from a string (starting at any position)

POSITION Returns the position of the first occurrence of a substring in a string

REPEAT Repeats a string as many times as specified

REPLACE Replaces all occurrences of a substring within a string, with a new substring

REVERSE Reverses a string and returns the result

RIGHT Extracts a number of characters from a string (starting from right)

RPAD Right-pads a string with another string, to a certain length

RTRIM Removes trailing spaces from a string

SPACE Returns a string of the specified number of space characters

STRCMP Compares two strings

SUBSTR Extracts a substring from a string (starting at any position)

SUBSTRING Extracts a substring from a string (starting at any position)

SUBSTRING_INDEX Returns a substring of a string before a specified number of delimiter occurs

TRIM Removes leading and trailing spaces from a string

UCASE Converts a string to upper-case

UPPER Converts a string to upper-case

Hi, Guys, As described in the intro of this video, Instead of making a single Video, i have divided exercises into Basic and Advanced. For Advanced Level of Exercise Switch to the Next Video.

Ex_1

SELECT REVERSE(UPPER('Hello World'));

Ex_2

SELECT

REPLACE

(

CONCAT('I',' ',' LIKE ',' ','MYSQL'),

' ',

'-'

);

Ex_3

SELECT

REPLACE(title, ' ', ' Space ') AS title

FROM books;

Ex_4

SELECT

REVERSE(author_fname) AS REVERSEfname,

REVERSe(author_lname) AS REVERSElanem

FROM books;

Ex_5

SELECT

UPPER(CONCAT(author_fname,' ',author_lname))

FROM books;

Ex_6

SELECT

title,

CHAR_LENGTH(title)

FROM books;

Ex_7

SELECT

CONCAT(author_fname,' ',author_lname) AS Author,

CONCAT(SUBSTRING(title,1,6),'..and so on') AS 'short_Title',

CONCAT(stock_quantity, ' in stock') As Available_Books

FROM books;

INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)

VALUES

('Fake_Book1', 'Fake1', 'Author1', 1945, 95, 100),

('Fake_Book2', 'Fake2', 'Author2', 2004, 172, 200),

('Fake_Book3', 'Fake3', 'Author3', 2005, 92, 300);

SELECT DISTINCT author_lname FROM books;

SELECT DISTINCT author_lname,author_fname FROM books;

SELECT DISTINCT CONCAT(author_lname,' ',author_fname) FROM books;

SELECT DISTINCT author_lname FROM books ORDER BY author_lname;

SELECT DISTINCT title FROM books ORDER BY title DESC;

SELECT DISTINCT title FROM books ORDER BY title ASC;

SELECT DISTINCT title,author_lname,released_year FROM books ORDER BY released_year DESC;

SELECT DISTINCT title,author_lname,released_year FROM books ORDER BY 3 DESC;

SELECT DISTINCT author_lname FROM books ORDER BY author_lname;

SELECT DISTINCT title FROM books ORDER BY title DESC;

SELECT DISTINCT title FROM books ORDER BY title ASC;

SELECT DISTINCT title,author_lname,released_year FROM books ORDER BY released_year DESC;

SELECT DISTINCT title,author_lname,released_year FROM books ORDER BY 3 DESC;

SELECT DISTINCT title,author_lname,released_year FROM books ORDER BY released_year,author_lname DESC;

SELECT DISTINCT title,author_lname,released_year FROM books ORDER BY 3,2 DESC;

SELECT title FROM books LIMIT 3;

SELECT title, released_year  FROM books ORDER BY released_year DESC LIMIT 3;

SELECT title, released_year  FROM books ORDER BY released_year ASC LIMIT 3;

SELECT title, released_year  FROM books ORDER BY released_year ASC LIMIT 4,6;

SELECT title, released_year  FROM books ORDER BY released_year ASC LIMIT 4,6546546464564;

SELECT title,author_fname FROM books WHERE author_fname LIKE '%ca%';

SELECT title,author_fname FROM books WHERE author_fname LIKE '%a%';

SELECT title,author_fname FROM books WHERE author_fname LIKE '%%%';

SELECT title,stock_quantity FROM books WHERE stock_quantity LIKE '___';

+39(312)645798 LIKE '+__(___)______'

%%%

EX1

SELECT title FROM books WHERE title LIKE '%The%';

EX2

SELECT title, pages FROM books ORDER BY pages LIMIT 1;

EX3

SELECT

CONCAT(title, ' has ', pages, ' pages ') AS Overview

FROM books ORDER BY pages LIMIT 3;

EX4

SELECT title, released_year, stock_quantity

FROM books ORDER BY stock_quantity ASC LIMIT 3;

EX5

SELECT CONCAT(UPPER(author_fname),' ', UPPER(author_lname),' has ', stock_quantity,' in stock which was released in ',

released_year) AS Exercise5 FROM books ORDER BY released_year DESC;

SELECT COUNT(*) FROM books;

SELECT COUNT(DISTINCT author_fname) FROM books;

SELECT COUNT(DISTINCT author_lname) FROM books;

SELECT COUNT(DISTINCT author_fname,author_lname) FROM books;

SELECT title FROM books WHERE title LIKE '%a%' ;

SELECT COUNT(*) FROM books WHERE title LIKE '%the%' ;

SELECT author_fname,author_lname FROM books;

SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname;

SELECT author_lname,author_fname,COUNT(*) FROM books GROUP BY author_lname,author_fname ORDER bY COUNT(*) DESC;

SELECT released_year,COUNT(*) FROM books GROUP BY released_year ORDER bY COUNT(*) ASC;

SELECT MIN(released_year) FROM books;

SELECT MAX(released_year) FROM books;

SELECT MAX(pages) FROM books;

SELECT * FROM books WHERE pages=634;

SELECT title, pages FROM books WHERE pages=(SELECT MAX(pages) FROM books);

SELECT title,pages FROM books ORDER BY pages DESC LIMIT 1;

SELECT author_fname,author_lname,MIN(released_year) FROM books GROUP BY author_fname,author_lname;

SELECT author_fname,author_lname,MAX(pages) FROM books GROUP BY author_fname,author_lname;

SELECT SUM(pages) FROM books;

SELECT author_fname,author_lname,SUM(pages) FROM books GROUP BY author_fname,author_lname ORDER BY SUM(pages) ASC LIMIT 1;

SELECT author_fname,author_lname,SUM(pages) FROM books GROUP BY author_fname,author_lname ORDER BY SUM(pages) DESC LIMIT 1;

SELECT AVG(pages) FROM books;

SELECT released_year,AVG(pages) FROM books GROUP BY released_year;

SELECT author_fname,author_lname,AVG(pages) FROM books GROUP BY author_fname,author_lname;

SELECT COUNT(*) FROM books;

SELECT released_year, COUNT(*) FROM books GROUP BY released_year;

SELECT sum(stock_quantity) FROM books;

SELECT author_fname,author_lname, AVG(pages) FROM books GROUP BY author_lname,author_fname;

SELECT CONCAT(author_fname,' ',author_lname),pages FROM books WHERE pages=(SELECT MIN(pages)FROM books);

SELECT author_fname,author_lname,COUNT(*) AS books,AVG(pages) AS Avg_pages FROM books GROUP BY author_fname,author_lname;