Posts

INDEX OF Zeek MySQL

Spring Blog Index Page Basic Concepts of SQL (1-3) OPEN/CLOSE No Title Labels 1 Data, DataBase, DataModel Types & Infrastructure Infrastructure for DATA MODEL Types Of Data Models What is data and database? 2 Concepts of DBMS,RDBMS ,SQL and DBMS vs RDBMS DBMS vs RDBMS RDBMS and its features and SQL features What is DBMS its benefits 3 SQL Starting(Introduction, Data-Types,Constraints and Command-Groups) SQL Data Types SQL Constraints SQL |...
Post a Comment
Read more

12 - Combining Two Tables- JOIN ( Inner , Left and Right)

Image
  What is join in SQL database? SQL JOIN is a clause that is used to combine multiple tables and retrieve data based on a common field in relational databases TABLES WITH NAME employees AND department_location WITH ONE COMMON COLUMN NAME department employees department_location INNER JOIN The INNER JOIN keyword selects records that have matching values in both tables. select e.first_name,e.salary,d.department,d.location from employees e inner join department_location d on e.department=d.department; LEFT JOIN The LEFT JOIN keyword returns all records from the left table  (table1), and the matching records from the right table (table2).  The result is 0 records from the right side, if there is no match select e.first_name,e.salary,d.department,d.location from employees e left join department_location d on e.department=d.department; RIGHT JOIN The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (ta...
Post a Comment
Read more

11 - UNION , UNION ALL

Image
  UNION  The UNION command combines the result set of two or more SELECT statements (only distinct values). UNION ALL The Union All extracts all the rows including the duplicates (repeated values) from both the queries. ORIGINAL GENERATED LIST       market_1                                                                               market_2                                    Make a final table from market_1 and market_2 which covers all products and  duplicate product becomes distinct select product from market_1 UNION select product from market_2; Make a final table from market_1 and market_2 which covers all products and  duplicate product stays duplicate se...
Post a Comment
Read more

10 - Arranging or Sorting the table in Ascending or Descending order(Order by)

Image
  ORIGINAL TABLE GENERATED Arrange or Sort the table in Ascending  order select * from employees   order by  salary asc ; Arrange or Sort the table in  Descending order select * from employees   order by  salary desc ;
Post a Comment
Read more

9 - Having Clause(To put condition on count)

Image
  ORIGINAL TABLE GENERATED Filter the Maximum Salary for department  whose count is 2 or more than 2 select * from employees where salary in ( select max (salary) from employees group by department having count (department)>= 2 ) ; Filter the Minimum Salary for department  whose count is 2 or more than 2 select * from employees where salary in ( select min (salary) from employees group by department having count (department)>= 2 ) ;
Post a Comment
Read more

8 - Filtering Max or Minimum Salary from each Department(Group By)

Image
  ORIGINAL LIST Filter the Maximum Salary and Its details from Each Department select * from employees where salary in ( select max (salary) from employees group by department); Filter the  Minimum Salary and Its details from Each Department select * from employees where salary in ( select min (salary) from employees group by department);
Post a Comment
Read more

7 - SQL Aggregation functions

Image
  Aggregation Functions Avg()         :      Returns the average value from specified columns Count()     :        Returns number of table rows Max()       :       Returns largest value among the records Min()        :      Returns smallest value among the records Sum()      :     Returns the sum of specified column values ...........................................................                                   ORIGINAL LIST Find the maximum salary out of the list using SQL Aggregation Functions select   max (salary) from employees ; Find the minimum salary out of the list using SQL Aggregation Functions select   min (salary) from employees ; Find the average salary  of the list using SQL Aggregation F...
Post a Comment
Read more

6 - Filtering Data by using (where , and , or , ! , > , <)

Image
  ORIGINAL TABLE GENERATED List from which we filter Data  data from table where emp_id has value 2 select * from employees where emp_id= 2 ; data from table where value of salary is 900 select * from employees where salary= 900 ; data from table where first_name is Tom as well as salary is 900 select * from employees where first_name= 'Tom' and salary= 900 ; data from table where either the last_name is Khan or salary is 8000 select * from employees where last_name= 'Khan' or salary= 8000 ; data from table where either the last_name is Khan or salary is greater than 80000 select * from employees where last_name= 'Khan' or salary> 80000 ; data from table where the last_name is Khan as well as salary is greater than 80000 select * from employees where last_name= 'Khan' and salary> 80000 ; data from table where either last_name is Geller or salary is not equal to 50000 select * from employees where last_name= 'Geller' o...
Post a Comment
Read more

5 - DML commands used in MySQL WorkBench

Image
DML(Data Manipulation Language)  CREATING A TABLE create table employees( emp_id int not null , first_name varchar ( 20 ) not null , last_name varchar ( 10 ), salary int , primary key (emp_id) ); INSERTING DATA IN TABLE insert into employees(emp_id,first_name,last_name,salary) values ( 1 , 'Zeeshan' , 'Khan' , 50000 ); insert into employees(emp_id,first_name,last_name,salary) values ( 2 , 'Ross' , 'Geller' , 60000 ); insert into employees(emp_id,first_name,last_name,salary) values ( 3 , 'Rachel' , 'Green' , 8000 ); insert into employees(emp_id,first_name,last_name,salary) values ( 4 , 'Joey' , 'Tribbiani' , 99000 ); select * from employees; UPDATING OR CHANGING DATA update employees set last_name= 'Geller' where emp_id= 3 ; select * from employees; DELETING DATA  delete from employees where emp_id= 4 ; select * from employees; OUTPUT GENERATED
Post a Comment
Read more

4 - DDL commands used in MySQL Workbench

Image
  I have done following things on MySQL workbench using DDL commands-- 1- Create a table name employee with emp_id , first_name , last_name , salary. 2-Add a column contact. 3- Rename the column contact to job_code. 4-Deleting the table   Create a table name employee with emp_id , first_name , last_name , salary. Table Generated looks like Logical structure of table Add a column contact. Seeing the new table Renaming column contact to job_code Seeing the table Deleting the table
Post a Comment
Read more

3 - SQL Starting(Introduction, Data-Types,Constraints and Command-Groups)

Image
  What is SQL? In simple words,SQL(Structured Query Language) is a programming language specifically defined for working with data-base to  --> CREATE --> MANIPULATE --> SHARE/ACCESS or SQL stands for Structured Query Language is used for storing and managing data in relational database management system (RDMS).  It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables. SQL Data Types Data types are used to represent the nature of the data that can be stored in the database table. For example, in a particular column of a table, if we want to store a string type of data then-->  we will have to declare a string data type of this column. Data types mainly classified into three categories for every database. --> String Data types --> Numeric Data types --> Date and time Data types 1- String                - char , varchar ,...
Post a Comment
Read more