
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;
Table 12.13 Date and Time Functions
Name Description
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
CONVERT_TZ() Convert from one time zone to another
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME() Return the current time
DATE() Extract the date part of a date or datetime expression
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format Unix timestamp as a date
GET_FORMAT() Return a date format string
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE() Create a date from the year and day of year
MAKETIME() Create time from hour, minute, second
MICROSECOND() Return the microseconds from argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
QUARTER() Return the quarter from a date argument
SEC_TO_TIME() Converts seconds to 'hh:mm:ss' format
SECOND() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
SUBTIME() Subtract times
SYSDATE() Return the time at which the function executes
TIME() Extract the time portion of the expression passed
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP() Return a Unix timestamp
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (1-53)
YEAR() Return the year
YEARWEEK() Return the year and week
CREATE TABLE country (name_VARCHAR VARCHAR(15), short_name_CHAR CHAR(5));
INSERT INTO country (name_VARCHAR, short_name_CHAR) VALUES ('United Kingdom', 'UK');
SELECT * FROM country;
INSERT INTO country (name_VARCHAR, short_name_CHAR) VALUES ('United States', 'US');
INSERT INTO country (name_VARCHAR, short_name_CHAR) VALUES ('Italy', 'ITA');
SELECT * FROM country;
INSERT INTO country (name_VARCHAR, short_name_CHAR) VALUES ('Dummsdsdfsdfsdfsdf', 'DummY123');
SELECT * FROM country;
CREATE TABLE DECIML(price DECIMAL(5,2));
INSERT INTO DECIML(price) VALUES(8);
INSERT INTO DECIML(price) VALUES(654654);
INSERT INTO DECIML(price) VALUES(123.45);
INSERT INTO DECIML(price) VALUES(8.13236);
DATETIME
CREATE TABLE attendance (name VARCHAR(100), entrydate DATE, entrytime TIME, entryDT DATETIME);
INSERT INTO attendance (name, entrydate, entrytime, entryDT)
VALUES('Lazy Worker', '2020-06-11', '08:07:35', '2020-06-11 08:07:35');
INSERT INTO attendance (name, entrydate, entrytime, entryDT)
VALUES('Sleepy Head', '2020-09-11', '10:30:35', '2020-06-11 10:30:35');
SELECT * FROM attendance;
DATETIME
CREATE TABLE attendance (name VARCHAR(100), entrydate DATE, entrytime TIME, entryDT DATETIME);
INSERT INTO attendance (name, entrydate, entrytime, entryDT)
VALUES('Lazy Worker', '2020-06-11', '08:07:35', '2020-06-11 08:07:35');
INSERT INTO attendance (name, entrydate, entrytime, entryDT)
VALUES('Sleepy Head', '2020-09-11', '10:30:35', '2020-06-11 10:30:35');
SELECT * FROM attendance;
INSERT INTO attendance (name, entrydate, entrytime, entryDT)
VALUES('Late Commer', CURDATE(), CURTIME(), NOW());
DATE_FORMAT
SELECT DATE_FORMAT(entryDT,'%m/%d/%y') FROM attendance;
DATEDIFF() Return Number of days
SELECT name,entryDT,DATEDIFF(NOW(),entryDT) FROM attendance;
DATE_ADD()
SELECT name,entryDT,DATE_ADD(entryDT,INTERVAL 1 MONTH) FROM attendance;
+/-
SELECT name,entryDT,entryDT + INTERVAL 3 MONTH + INTERVAL 5 HOUR + INTERVAL 53 MINUTE FROM attendance;
CREATE TABLE obervation(content VARCHAR(100)),created_at TIMESTAMP DEFAULT NOW());
INSERT INTO observation(content) VALUES('Hi, I arrived before 7 Oclock today');
INSERT INTO observation(content) VALUES('Hi, I arrived before 6 Oclock today in the Morning');
INSERT INTO observation(content) VALUES('Hi, I arrived before 8.30 Oclock today');
SELECT * FROM observation;
XERCISE:SEC 10
CHAR AND VAR_CHAR BOTH ARE STRING DATA TYPE
23.36 is a INT
FLOATING AND DOUBLE DATA TYPES ONLY DIFFER IN PRECISION
Exercise:Final
CREATE TABLE obsrvation1(name VARCHAR(100),content VARCHAR(100),created_at TIMESTAMP DEFAULT NOW());
INSERT INTO obsrvation1(name,content) VALUES('Lazy Head','Hi, I arrived before 7 Oclock today');
INSERT INTO obsrvation1(name,content) VALUES('Sleepy Fellow','Hi, I arrived before 6 Oclock today in the Morning');
INSERT INTO obsrvation1(name,content) VALUES('Chuck Norris','Hi, I arrived before 8.30 Oclock today');
SELECT * FROM obsrvation1;
As Ex-Assistant Professor,I distilled all my experience in this course,This course is not watch me over the shoulder, but full of exercises, challenges and quizzes and you will get your hands dirty with coding from the beginning.
Are your Confused about MySQL and SQL? MySQL is Database Management System, while SQL is a language.
Do you want to learn SQL and but you dont know where to start.Do you want to learn MySQL but are too intimidated. Skills you will learn here can apply to MongoDB, PostgresSQL,and many more.
You want to get the most in demand skill by recent employer Survey,but, don't know which is the most comprehensive course. Congratulations.
You have made the right choice,
This course will take you from beginner from No, Coding or Database experience in SQL and MySQL to an professional level where You will easily be able to understand and use concepts like Aggregate functions, Many to Many relations and Much More advanced concepts along with their application to real world databases which will be provided in the course.
In this course along with many things, you learn
1- Analysed a Linkedin Database
2- Write Quarry and Built Udemy Course review App.
3- Built MySQL/SQL powered Web Application as Bonus Section.
4- Solve More than 120 Exercises and Challenges.
5- You will work with large databases mimicking real world application.
6- 17 Hours of exhaustive SQL Programming in which you will learn to see data from different perspective.
7- Entire Sections Dedicated to Exercises and Challenges which will test your Knowledge.
8- You will learn about Django Web Frame work and How to connect it with MySQL as Bonus
9- You will learn how to write and Execute SQL Quarries in Python code As Bonus.
Here is what other Students like you say about this course.
Aditya Bohra-5 Stars
Easy Explanation given by the sir, I am very thankful to the sir because the fear of coding for me is gone now , The way of teaching is really awesome. This course is really good for the beginners.
Gaurav kumar-5 Stars
this great course in Hindi. you should make a course for python.
Prasad Gundelli-5 Stars
I literally loved the way of explanation.
Sajid Shaikh-5 Stars
Explanations were crystal clear and tutor is friendly as well, really loved this course. Would recommend for people who speak Urdu and Hindi
Xmart Rocky-5 Stars
Amazing Course With Funniest Teacher.
This course comes with Udemy's 30 days Money Back guarantee, This is the deal you can never go wrong
I have 100% response rate , so I will always be there to respond to your questions. And of course after Completing this course not only you will built your portfolio but also get Certificate of completion which you can post on your linked Profile and Attract potential Employers.
Enroll now and lets get started...