Selenium, Java, testNG, Apache POI, Maven, Log4j, Extent report, C#, python and Asp.net blog

Posts

Showing posts with the label MYSQL PRACTICAL EXAMPLE SCRIPTS

MySQL practical Tutorials part 12- foreign key and different types of joins(inner join, left join, right join, cross join) and multiple join exercises

======================================================================== Working With Foreign Keys -- Creating the customers and orders tables CREATE TABLE customers( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100) ); CREATE TABLE orders( id INT AUTO_INCREMENT PRIMARY KEY, order_date DATE, amount DECIMAL(8,2), customer_id INT, FOREIGN KEY(customer_id) REFERENCES customers(id) ); -- Inserting some customers and orders INSERT INTO customers (first_name, last_name, email) VALUES ('Boy', 'George', 'george@gmail.com'), ('George', 'Michael', 'gm@gmail.com'), ('David', 'Bowie', 'david@gmail.com'), ('Blue', 'Steele', 'blue@gmail.com'), ('Bette', 'Davis', 'bette@aol.com'); ...

MySQL practical Tutorials part 11- MySQL IN, CASE statement and some practical exercises on logical operators.

========================================================================= In And Not In show databases(); use book_shop; SELECT title, author_lname FROM books WHERE author_lname='Carver' OR author_lname='Lahiri' OR author_lname='Smith'; SELECT title, author_lname FROM books WHERE author_lname IN ('Carver', 'Lahiri', 'Smith'); SELECT title, released_year FROM books WHERE released_year IN (2017, 1985); SELECT title, released_year FROM books WHERE released_year != 2000 AND released_year != 2002 AND released_year != 2004 AND released_year != 2006 AND released_year != 2008 AND released_year != 2010 AND released_year != 2012 AND released_year != 2014 AND released_year != 2016; SELECT title, released_y...

MySQL practical Tutorials part 10- MySQL logical AND, logical OR and BETWEEN operator.

MySQL logical AND, logical OR and BETWEEN operator practical queries. ======================================================================================================================= Logical AND SELECT title, author_lname, released_year FROM books WHERE author_lname='Eggers'; SELECT title, author_lname, released_year FROM books WHERE released_year > 2010; SELECT title, author_lname, released_year FROM books WHERE author_lname='Eggers' AND released_year > 2010; SELECT 1 < 5 && 7 = 9; -- false SELECT -10 > -20 && 0 <= 0; -- true SELECT -40 <= 0 AND 10 > 40; --false SELECT 54 <= 54 && 'a' = 'A'; -- true SELECT * FROM books WHERE author_lname='Eggers' AND released_year > 2010 AND title LIKE '%novel%'; Please note, as of MySQL 8.0.17, the && o...

MySQL practical Tutorials part 9- SQL not operator, SQL Not Like, SQL greater than, SQL less than greater than operator

========================================================================= Not Equal SELECT title FROM books WHERE released_year = 2017; SELECT title FROM books WHERE released_year != 2017; SELECT title, author_lname FROM books; SELECT title, author_lname FROM books WHERE author_lname = 'Harris'; SELECT title, author_lname FROM books WHERE author_lname != 'Harris'; ========================================================================= Not Like SELECT title FROM books WHERE title LIKE 'W'; SELECT title FROM books WHERE title LIKE 'W%'; SELECT title FROM books WHERE title LIKE '%W%'; SELECT title FROM books WHERE title LIKE 'W%'; SELECT title FROM books WHERE title NOT LIKE 'W%'; ========================================================================= Greater Than SELECT title, released_year FROM books ORDER BY released_year; SELECT title, released_year FROM books WHERE released_year > 2000 ORDER BY release...

MySQL practical Tutorials part 8- SQL date and timestamp functions and miscellaneous exercises on that

Date Math SELECT * FROM people; SELECT DATEDIFF(NOW(), birthdate) FROM people; SELECT name, birthdate, DATEDIFF(NOW(), birthdate) FROM people; SELECT birthdt FROM people; SELECT birthdt, DATE_ADD(birthdt, INTERVAL 1 MONTH) FROM people; SELECT birthdt, DATE_ADD(birthdt, INTERVAL 10 SECOND) FROM people; SELECT birthdt, DATE_ADD(birthdt, INTERVAL 3 QUARTER) FROM people; SELECT birthdt, birthdt + INTERVAL 1 MONTH FROM people; SELECT birthdt, birthdt - INTERVAL 5 MONTH FROM people; SELECT birthdt, birthdt + INTERVAL 15 MONTH + INTERVAL 10 HOUR FROM people; ======================================================================================================================= Working with TIMESTAMPS CREATE TABLE comments ( content VARCHAR(100), created_at TIMESTAMP DEFAULT NOW() ); INSERT INTO comments (content) VALUES('lol what a funny article'); INSERT INTO comments (content) VALUES...

MySQL practical Tutorials part 7- sql Date Float, double , Date datatype all queries

Mysql Date Float, double , Date datatype all queries ======================================================================================================================= FLOAT and DOUBLE CREATE TABLE thingies (price FLOAT); INSERT INTO thingies(price) VALUES (88.45); SELECT * FROM thingies; INSERT INTO thingies(price) VALUES (8877.45); SELECT * FROM thingies; INSERT INTO thingies(price) VALUES (8877665544.45); SELECT * FROM thingies; ======================================================================================================================= Creating Our DATE data CREATE TABLE people (name VARCHAR(100), birthdate DATE, birthtime TIME, birthdt DATETIME); INSERT INTO people (name, birthdate, birthtime, birthdt) VALUES('Padma', '1983-11-11', '10:07:35', '1983-11-11 10:07:35'); INSERT INTO people (name, birthdate, birthtime, birthdt) VALUES('Larry', '1943-12-25', '04:10:42', '1943-12-25 04:10:42');...

MySQL practical Tutorials part 6- SQL min, max, sum, average functions. Difference between char and varchar in SQL. Decimal data type in SQL.

Below are the some practical queries based on Mysql important SQL min, max, sum, average functions. Difference between char and varchar in SQL. Decimal data type in SQL. ======================================================================================================================= Using Min and Max with Group By SELECT author_fname, author_lname, Min(released_year) FROM books GROUP BY author_lname, author_fname; SELECT author_fname, author_lname, Max(pages) FROM books GROUP BY author_lname, author_fname; SELECT CONCAT(author_fname, ' ', author_lname) AS author, MAX(pages) AS 'longest book' FROM books GROUP BY author_lname, author_fname; =========================================================================================...

MySQL practical Tutorials part 5- SQL count function, group by clause, MIN and MAX function

Below are the practical queries for count function and group by clause in mysql databases. Please refer previous tutorials to get how to create database and tables and fill data into it and then refer below practical queries. when you go step by steps then you will get the flow of tutorials. Thank you. ======================================================================================================================= CODE: The Count Function SELECT COUNT(*) FROM books; SELECT COUNT(author_fname) FROM books; SELECT COUNT(DISTINCT author_fname) FROM books; SELECT COUNT(DISTINCT author_lname) FROM books; SELECT COUNT(DISTINCT author_lname, author_fname) FROM books; SELECT title FROM books WHERE title LIKE '%the%'; SELECT COUNT(*) FROM books WHERE title LIKE '%the%'; ======================================================================================================================= CODE: The Joys of Group By SELECT title, author_lname FR...

MySQL practical Tutorials part 4- Using Distinct, order by(asc, desc) clause, limit and wildcard character (like keyword)

======================================================================================================================= Examples od distinct keyword in mysql. Please refer previous articles for create database/table commands. Then you can refer this for further queries. Kindly follow step by step articles. CODE: Using DISTINCT SELECT author_lname FROM books; SELECT DISTINCT author_lname FROM books; SELECT author_fname, author_lname FROM books; SELECT DISTINCT CONCAT(author_fname,' ', author_lname) FROM books; SELECT DISTINCT author_fname, author_lname FROM books; ======================================================================================================================= CODE: Sorting Data with ORDER BY SELECT author_lname FROM books; SELECT author_lname FROM books ORDER BY author_lname; SELECT title FROM books; SELECT title FROM books ORDER BY title; SELECT author_lname FROM books ORDER BY author_lname DESC; SELECT...

MySQL practical Tutorials part 3- String Functions CONCAT, SUBSTRING, REVERSE, CHAR_LENGTH, REPLACE

CODE: Loading Our Book Data 1. First create TABLE with the following code: DROP DATABASE IF EXISTS book_shop; CREATE DATABASE book_shop; USE book_shop; 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 Adv...

MySQL practical Tutorials part 2- inserting data into tables, CRUD, multiple inserts, warnings, primary key, not null, auto increment

CODE: Inserting Data Inserting Data The "formula": INSERT INTO table_name(column_name) VALUES (data); For example: INSERT INTO cats(name, age) VALUES ('Jetson', 7); SELECT * FROM cats; INSERT INTO table_name (column_name, column_name) VALUES (value, value), (value, value), (value, value); INSERT Challenge Solution Code CREATE TABLE people ( first_name VARCHAR(20), last_name VARCHAR(20), age INT ); INSERT INTO people(first_name, last_name, age) VALUES ('Tina', 'Belcher', 13); INSERT INTO people(age, last_name, first_name) VALUES (42, 'Belcher', 'Bob'); INSERT INTO people(first_name, last_name, age) VALUES('Linda', 'Belcher', 45) ,('Phillip', 'Frond', 38) ,('Calvin', 'Fischoeder', 70); DROP TABLE people; SELECT * FROM people; sho...

MySQL practical Tutorials part 1- creating databases, tables and fill data to it and delete databases

List available databases: show databases; The general command for creating a database: CREATE DATABASE database_name; A specific example: CREATE DATABASE soap_store; CODE: Dropping Databases To drop a database: DROP DATABASE database_name; For Example: DROP DATABASE hello_world_db; Remember to be careful with this command! Once you drop a database, it's gone! CREATE DATABASE soap_store; CODE: Creating Your Own Tables CREATE TABLE tablename ( column_name data_type, column_name data_type ); CREATE TABLE cats ( name VARCHAR(100), age INT ); CODE: How Do We Know It Worked? SHOW TABLES; SHOW COLUMNS FROM tablename; DESC tablename; Dropping Tables DROP TABLE <tablename>; A specific example: DROP TABLE cats; CREATE TABLE pastries ( name VARCHAR(50), quantity INT ); SHOW TABLES; DESC pastries; DROP TABLE pastries;