Corona Virus And DNA of Corona Virus | Novel Corona Virus COVID-19

15 Select SQL Queries for mysql mariadb database on xampp server

Sample Table for Reference:

Table Name:
      person
Fields:
      id(INT), name(VARCHAR), designation(VARCHAR), city(VARCHAR), age(INT)

How to select database?

Syntax:
       USE database_name

Example:
       USE test;

How to create table?

Syntax:
      CREATE TABLE table_name (column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] [PRIMARY KEY], ...)

Example:
       CREATE TABLE person (id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255), designation VARCHAR(50),
city VARCHAR(50),
age INT(3));

How to check table structure?

Syntax:
        DESC table_name;

Example:
        DESC person;

1. How to SELECT all records?

Syntax:

SELECT column1, column2, ... FROM table_name; SELECT * FROM table_name;

Example:
      SELECT name, city FROM person; SELECT * FROM person;

2. How to SELECT distinct (different) records?

Syntax:

      SELECT DISTINCT column FROM table_name;
      SELECT COUNT(DISTINCT column) FROM table_name;

Example:

      SELECT DISTINCT city FROM person;
      SELECT COUNT(DISTINCT city) FROM person;

3. How to SELECT records based on condition?

Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition;
SELECT * FROM table_name WHERE condition;

Example:

SELECT name, city FROM person WHERE id=3;
SELECT * FROM person WHERE name="Aris Singh";

Note: Use Operators : =, <, >, <=, >=, <>, IN, LIKE, BETWEEN

4. How to SELECT records based on operators AND, OR & NOT?

AND: All conditions satisfied

OR: Any one of condition satisfied

NOT: Condition not TRUE

AND Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3;

SELECT * FROM table_name WHERE condition1 AND condition2 AND condition3;

Example:

SELECT name, city FROM person WHERE id=3 AND designation="Computer Operator";

SELECT * FROM person WHERE id=3 AND designation="Computer Operator";

OR Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3;

SELECT * FROM table_name WHERE condition1 OR condition2 OR condition3;

Example:

SELECT name, city FROM person WHERE id=3 OR designation="Computer Operator";

SELECT * FROM person WHERE id=3 OR designation="Computer Operator";

NOT Syntax:

SELECT column1, column2, ... FROM table_name WHERE NOT condition;

SELECT * FROM table_name WHERE NOT condition;

Example:

SELECT name, city FROM person WHERE NOT city="Kanpur";

SELECT * FROM person WHERE NOT city="Kanpur";

AND - OR Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition1 AND (condition2 OR condition3;

SELECT * FROM table_name WHERE condition1 AND (condition2 OR condition3);

Example:

SELECT name, city FROM person WHERE id=3 AND (designation="Computer Operator" OR city="Delhi");

SELECT * FROM person WHERE id=3 AND (designation="Computer Operator" OR city="Delhi");

AND - NOT Syntax:

SELECT column1, column2, ... FROM table_name WHERE NOT condition1 AND NOT condition2;

SELECT * FROM table_name WHERE NOT condition1 AND NOT condition2;

Example:

SELECT name, city FROM person WHERE NOT designation="Computer Operator" AND NOT city="Kanpur";

SELECT * FROM person WHERE NOT designation="Computer Operator" AND NOT city="Kanpur";

5. How to SELECT records in ascending or descending order?

Syntax:

SELECT column1, column2, ... FROM table_name ORDER BY column ASC | DESC;

SELECT * FROM table_name ORDER BY column ASC | DESC;

Example:

SELECT name, city FROM person ORDER BY name ASC;

SELECT name, city FROM person ORDER BY name;

SELECT name, city FROM person ORDER BY name DESC;

SELECT * FROM person ORDER BY name ASC;

SELECT * FROM person ORDER BY name;

SELECT * FROM person ORDER BY name DESC;

6. How to SELECT top records?

Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition LIMIT number;

SELECT * FROM table_name WHERE condition LIMIT number;

Example:

SELECT name, city FROM person WHERE age<40 LIMIT 4;

SELECT * FROM person WHERE age<40 LIMIT 4;

7. How to SELECT based on minimum and maximum values?

Syntax:

SELECT MIN(column) FROM table_name WHERE condition;

SELECT MAX(column) FROM table_name WHERE condition;

Example:

SELECT MIN(age) FROM person WHERE city='Greater Noida';

SELECT MAX(age) FROM person WHERE city='Greater Noida';

8. How to SELECT records with COUNT, SUM & AVG functions?

Syntax:

SELECT COUNT(column) FROM table_name WHERE condition;

SELECT SUM(column) FROM table_name WHERE condition;

SELECT AVG(column) FROM table_name WHERE condition;

Example:

SELECT COUNT(city) FROM person WHERE city='Noida';

SELECT SUM(age) FROM person WHERE designation='Engineer';

SELECT AVG(age) FROM person WHERE designation='Engineer';

9. How to SELECT records with pattern matching?

Syntax:

SELECT column1, column2, ... FROM table_name WHERE column LIKE pattern;

Note: '%' (Sequence of chars) and '_' (One char) wildcards are used with LIKE  operators

Patterns:

'a%' = Finds any values that start with "a"

'%a' = Finds any values that ends with "a"

'%a%' = Finds any values that have "a" in any position

'_a%' = Finds any values that have "a" in the second position

'a___%' = Finds any values that start with "a" and are at least 4 chars in length

'a%a' = Finds any values that start with "a" and ends with "a"

Example:

SELECT * FROM person WHERE city LIKE 'N%';

SELECT * FROM person WHERE city LIKE '%i';

SELECT * FROM person WHERE city LIKE '%o%';

SELECT * FROM person WHERE city LIKE '_o%';

SELECT * FROM person WHERE city LIKE 'N___%';

SELECT * FROM person WHERE city LIKE 'A%a';

10. How to SELECT records with IN & NOT IN operator?

Syntax:

SELECT * FROM table_name WHERE column IN (value1, value2, ...);

SELECT * FROM table_name WHERE column IN (SELECT statement);

SELECT * FROM table_name WHERE column NOT IN (value1, value2, ...);

SELECT * FROM table_name WHERE column NOT IN (SELECT statement);

Example:

SELECT * FROM person WHERE city IN ('Noida', 'Delhi', 'Kanpur');

SELECT * FROM person WHERE city IN (SELECT city from person WHERE designation='Engineer');

SELECT * FROM person WHERE city NOT IN ('Noida', 'Delhi', 'Kanpur');

SELECT * FROM person WHERE city NOT IN (SELECT city from person WHERE designation='Engineer');

11. How to SELECT records with BETWEEN operator?

Syntax:

SELECT column1, column2, ... FROM table_name WHERE column BETWEEN value1 AND value2;

SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;

Example:

SELECT name, city FROM person WHERE age BETWEEN 20 AND 30;

SELECT * FROM person WHERE age BETWEEN 20 AND 30;

12. How to SELECT records using GROUP BY statement?

Syntax:

SELECT column1, column2, ... FROM table_name GROUP BY column1, column2, ...;

Example:

SELECT name, city FROM person GROUP BY city;

SELECT * FROM person GROUP BY name, city;

Note:

The GROUP BY statement is generally used with aggregate functions as COUNT, MAX, MIN, SUM & AVG.

13. How to SELECT records using HAVING statement?

Syntax:

SELECT column1, column2, ... FROM table_name HAVING condition;

Example:

SELECT name, city FROM person HAVING AVG(age) >18;

SELECT * FROM person HAVING AVG(age) > 18;

Syntax:

SELECT column1, column2, ... FROM table_name WHERE column operator ANY (SELECT statement);

SELECT column1, column2, ... FROM table_name WHERE column operator ALL (SELECT statement);

14. How to SELECT records with ANY & ALL operators?

Syntax:

SELECT column1, column2, ... FROM table_name WHERE column operator ANY (SELECT
statement); SELECT column1, column2, ... FROM table_name WHERE column operator ALL (SELECT
statement);
Example:

SELECT name, city FROM person WHERE age > ALL (SELECT age from person WHERE city='Kanpur');

SELECT name, city FROM person WHERE age = ANY (SELECT age from person WHERE city='Kanpur');

Use Operators:

        =, !=, <, >, <=, >=, <>

15. How to SELECT records with IS NULL & IS NOT NULL?

Syntax:

SELECT column1, column2, ... FROM table_name WHERE column IS NULL;

SELECT column1, column2, ... FROM table_name WHERE column IS NOT NULL;

Example:

SELECT name, city FROM person WHERE designation IS NULL;

SELECT name, city FROM person WHERE designation IS NOT NULL;

How to Insert, Update & Delete data in table Mysql Mariadb database on xampp server?

Sample Table for Reference:

Table Name:
      person
Fields:
      id(INT), name(VARCHAR), designation(VARCHAR), city(VARCHAR), age(INT)

How to select database?

Syntax:
       USE database_name

Example:
       USE test;

How to create table?

Syntax:
      CREATE TABLE table_name (column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] [PRIMARY KEY], ...)

Example:
       CREATE TABLE person (id INT(11) AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255), designation VARCHAR(50),
city VARCHAR(50),
age INT(3));

How to check table structure?

Syntax:
        DESC table_name;

Example:
        DESC person;

How to insert record in a table?

Syntax:

INSERT INTO  table_name (column) VALUE (value);
INSERT INTO  table_name (column1, column2, ...) VALUES (value1, value2, ...);
INSERT INTO  table_name VALUES (value1, value2, valueN);

Example:
INSERT INTO person (name) VALUE ('Amita');
INSERT INTO person (name, city) VALUES ('Akash', 'Greater Noida');
INSERT INTO person VALUES ('Aviral Singh', 'Engineer', 'Kanpur', 23);

How to update record(s) in a table?

Syntax:

UPDATE table_name SET column1=value1, column2=value2, ... WHERE condition;

Example:

UPDATE person SET name='Aarav Singh', city='Greater Noida' WHERE id=2;

Delete records in the table

Note: If where condition is ignored all records will be update.

How to delete record(s) in a table?

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM person WHERE age < 18;

Note: If where condition is ignored all records will be delete.

How to delete table?

Syntax:

       DROP table table_name;

Example:

       DROP table sample;

What is Smoke Testing?

SMOKE TESTING

The name Smoke comes from hardware testing (electrical) looking for smoke when powering electrical items for the first time.

A smoke test may address basic questions like :

     1. Does the program run?
     2. Does the user interface open?
     3. Does clicking the main button do anything?

* The term originates in hardware repair and has been applied to software.

* It is intended to be a quick test to see if the application "catches on fire" when run for the first time.

* It is just to make sure you don't waste a bunch of folks time by setting them loose on something that's obviously broken.

* In software testing, smoke testing also know as :

1. Confidence testing
2. Sanity testing
3. Build verification test (BVT)
4. Build acceptance test.

* This is the first testing on the initial build.

* The process of smoke testing aims to determine whether the application is so badly broken as to make further immediate testing unnecessary.

* It aims to ensuring that the most important functions work. It is used to decide if a build is stable enough to proceed with further testing.

How to screw an object or how to create spring in blender

How to spin an object or how to create gear in blender

How to create water effect on text in Blender