MySQL

Why MySQL?

Many of the world’s largest and fastest-growing organizations including Facebook, Google, Adobe, Alcatel Lucent and Zappos rely on MySQL to save time and money powering their high-volume Web sites, business-critical systems and packaged software.

Traning Summary


MySQL is the most popular open-source database. This course starts with database basics, normalization and MySQL Workbench installation. Later it teaches MySQL commands like Select, Insert, Group By and advance topics like Wildcards & Functions.

What is Data?

In simple words data can be facts related to any object in consideration.

For example your name, age, height, weight etc are some data related to you.

A picture, image, file, pdf etc can also be considered data.

What is a Database?

Database is a systematic collection of data. Databases support storage and  manipulation of data. Databases make data management easy. Let’s discuss few examples.

An online telephone directory would definitely use database to store data pertaining to people, phone numbers, other contact details, etc.

Your electricity service provider is obviously using a database to manage billing , client related issues, to handle fault data, etc.

Let’s also consider the facebook. It needs to store, manipulate and present data related to members, their friends, member activities, messages, advertisements and lot more.

We can provide countless number of examples for usage of databases .

What is a Database Management System (DBMS)?

Database Management System (DBMS) is a collection of programs which enables its users to access database, manipulate data, reporting / representation of  data .

It also helps to control access to the  database.

Database Management Systems are not a new concept and as such had been first implemented in 1960s.

Charles Bachmen’s Integrate Data Store (IDS) is said to be the first DBMS in history.

With time database technologies evolved a lot while usage and expected functionalities of databases have been increased immensely.

Types of DBMS

Let’s see how the DBMS family got evolved with the time. Following diagram shows the evolution of DBMS categories.

There are 4 major types of DBMS. Let’s look into them in detail.

  • Hierarchical – this type of DBMS employs the “parent-child” relationship of storing data. This type of DBMS is rarely used nowadays. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes.
  • Network DBMS – this type of DBMS supports many-to many relations. This usually results in complex database structures.  RDM Server is an example of a database management system that implements the network model.
  • Relational DBMS – this type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships.Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server database.
  • Object Oriented Relation DBMS – this type supports storage of new data types. The data to be stored is in form of objects. The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data. PostgreSQL is an example of an object oriented relational DBMS.

What is SQL?

Structured Query language (SQL) pronounced as “S-Q-L” or sometimes as “See-Quel“is actually the standard language for dealing with Relational Databases.

SQL programming can be effectively used to insert, search, update, delete database records.

That doesn’t mean SQL cannot do things beyond that.

In fact it can do lot of things including, but not limited to, optimizing and maintenance of databases.

Relational databases like MySQL Database, Oracle, Ms SQL server, Sybase, etc uses SQL ! How to use sql syntaxes?

SQL syntaxes used in these databases are almost similar, except the fact that some are using few different syntaxes and even proprietary SQL syntaxes.

What is MySQL?

MySQL is an open source relational database.

MySQL is cross platform which means it runs on a number of different platforms such as Windows, Linux, and Mac OS etc.

Why use MySQL?

There are a number of relational database management systems on the market.

Examples of relational databases include Microsoft SQL Server, Microsoft Access, Oracle, DB2 etc.

One may ask why we would choose MySQL over the other database management systems.

The answer to this question depends on a number of factors. 

Difference between SQL and MySQL

Parameter SQL MYSQL
Definition SQL is a Structured Query Language. It is useful to manage relational databases. MySQL is an RDBMS tostore, retrieve, modify and administrate a database usingSQL.
Complexity You need to learn the SQL language to use it effectively. It is readily available through download and installation.
Type SQL is a query language. MySQL is database software. It used “SQL” language to query the database.
Support for connector SQL does not provide connectors. MySQL offers an integrated tool called ‘MySQL workbench’ to design and develop databases.
Purpose To query and operate database system. Allows data handling, storing, modifying, deleting in a tabular format.
Usage SQL code and commands are used in various DBMS and RDMS systems including MYSQL. MYSQL is used as an RDBMS database.
Updates The language is fixed, and command remains the same. Get the frequent updates

What is Database Design?

    Database Design is a collection of processes that facilitate the designing, development, implementation and maintenance of enterprise data management systems

It helps produce  database systems

  1. That meet the requirements of the users
  2. Have high performance.

Why Database Design is Important ?

Database designing is crucial to high performance database system.

Apart from improving the performance, properly designed database are easy to maintain, improve data consistency and are cost effective in terms of disk storage space.

Note , the genius of a database is in its design . Data operations using SQL is relatively simple.

What is ER Modeling?

Entity Relationship Modeling (ER Modeling) is a graphical approach to database design. It uses Entity/Relationship to represent real world objects.

An Entity is a thing or object in real world that is distinguishable from surrounding environment. For example each employee of an organization is a separate entity. Following are some of major characteristics of entities.

  • An entity has a set of properties.
  • Entity properties can  have values.

Steps for Create Database Mysql.

Create Database in two ways

1) By executing a simple SQL query

2) By using forward engineering in MySQL Workbench.

DATA TYPES

Data types define the nature of the data that can be stored in a particular column of a table

MySQL has 3 main categories of data types namely

  1.   Numeric,
  2.   Text
  3.   Date/time.

What is the WHERE Clause?

We looked at how to query data from a database using the SELECT statement in the previous tutorial. The SELECT statement returned all the results from the queried database table.

They are however, times when we want to restrict the query results to a specified condition. The SQL WHERE clause comes in handy in such situations.

What is PHP?

PHP is a general purpose server side scripting language that we can use to develop dynamic web sites and applications. PHP only needs to be installed on the web server that will host the web application and client applications can access the server resources via web browsers. The diagram shown below illustrates the basic architecture of a PHP web application.

Why PHP?

You have obviously head of a number of programming languages out there; you may be wondering why we would want to use PHP over other languages to develop our video library application? PHP just like MySQL is open source and free, you don’t need to pay for you to get PHP; all you have to do is download it. Packages such as XAMPP come bundled already with a web server, MySQL and PHP among others. This is also unbelievably free. This makes PHP a cost effective choice compared to other scripting languages such as CFML or ASP.

Other benefit that you get with PHP is that it’s a server side scripting language; this means you only need to install it on the server and client computers requesting for the resources from the server do not need to have PHP installed; only a web browser would be enough.

PHP also has in built support for working hand in hand with MySQL; this doesn’t mean you can’t use PHP with other database management systems.

PHP is cross platform which means you can deploy your application on a number of different operating systems such as windows, Linux, Mac OS etc.

Top 25 SQL Interview Questions & Answers

1. What is DBMS?

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS can be termed as File Manager that manages data in a database rather than saving it in file systems.

2. What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS store the data into the collection of tables, which is related by common fields between the columns of the table. It also provides relational operators to manipulate the data stored into the tables.

Example: SQL Server.

3. What is SQL?

SQL stands for Structured Query Language , and it is used to communicate with the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a database.

Standard SQL Commands are Select.

4. What is a Database?

Database is nothing but an organized form of data for easy access, storing, retrieval and managing of data. This is also known as structured form of data which can be accessed in many ways.

Example: School Management Database, Bank Management Database.

5. What are tables and Fields?

A table is a set of data that are organized in a model with Columns and Rows. Columns can be categorized as vertical, and Rows are horizontal. A table has specified number of column called fields but can have any number of rows which is called record.

Example:.

Table: Employee.

Field: Emp ID, Emp Name, Date of Birth.

Data: 201456, David, 11/15/1960.

6. What is a primary key?

A primary key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

7. What is a foreign key?

A foreign key is one table which can be related to the primary key of another table. Relationship needs to be created between two tables by referencing foreign key with the primary key of another table.

8. What is a join?

This is a keyword used to query data from more tables based on the relationship between the fields of the tables. Keys play a major role when JOINs are used.

9. What is normalization?

Normalization is the process of minimizing redundancy and dependency by organizing fields and table of a database. The main aim of Normalization is to add, delete or modify field that can be made in a single table.

 

10. What is a View?

A view is a virtual table which consists of a subset of data contained in a table. Views are not virtually present, and it takes less space to store. View can have data of one or more tables combined, and it is depending on the relationship.

11. What is an Index?

An index is performance tuning method of allowing faster retrieval of records from the table. An index creates an entry for each value and it will be faster to retrieve data.

12. What is a Cursor?

A database Cursor is a control which enables traversal over the rows or records in the table. This can be viewed as a pointer to one row in a set of rows. Cursor is very much useful for traversing such as retrieval, addition and removal of database records.

13. What is a relationship and what are they?

Database Relationship is defined as the connection between the tables in a database. There are various data basing relationships, and they are as follows:.

  • One to One Relationship.
  • One to Many Relationship.
  • Many to One Relationship.
  • Self-Referencing Relationship.

14. What is a query?

A DB query is a code written in order to get the information back from the database. Query can be designed in such a way that it matched with our expectation of the result set. Simply, a question to the Database.

15. What is subquery?

A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery. SubQuery is always executed first, and the result of subquery is passed on to the main query.

16. What is a stored procedure?

Stored Procedure is a function consists of many SQL statement to access the database system. Several SQL statements are consolidated into a stored procedure and execute them whenever and wherever required.

17. What is a trigger?

A DB trigger is a code or programs that automatically execute with response to some event on a table or view in a database. Mainly, trigger helps to maintain the integrity of the database.

Example: When a new student is added to the student database, new records should be created in the related tables like Exam, Score and Attendance tables.

18. What is the difference between DELETE and TRUNCATE commands?

DELETE command is used to remove rows from the table, and WHERE clause can be used for conditional set of parameters. Commit and Rollback can be performed after delete statement.

TRUNCATE removes all rows from the table. Truncate operation cannot be rolled back.

19. What are local and global variables and their differences?

Local variables are the variables which can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred or used. Variables can be created whenever that function is called.

Global variables are the variables which can be used or exist throughout the program. Same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

20. What is a constraint?

Constraint can be used to specify the limit on the data type of table. Constraint can be specified while creating or altering the table statement. Sample of constraint are.

  • NOT NULL.
  • CHECK.
  • DEFAULT.
  • UNIQUE.
  • PRIMARY KEY.
  • FOREIGN KEY.

21. What is data Integrity?

Data Integrity defines the accuracy and consistency of data stored in a database. It can also define integrity constraints to enforce business rules on the data when it is entered into the application or database.

22. What is Auto Increment?

Auto increment keyword allows the user to create a unique number to be generated when a new record is inserted into the table. AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.

Mostly this keyword can be used whenever PRIMARY KEY is used.

23. What is the difference between Cluster and Non-Cluster Index?

Clustered index is used for easy retrieval of data from the database by altering the way that the records are stored. Database sorts out rows by the column which is set to be clustered index.

A nonclustered index does not alter the way it was stored but creates a complete separate object within the table. It point back to the original table rows after searching.

24. What is Datawarehouse?

Datawarehouse is a central repository of data from multiple sources of information. Those data are consolidated, transformed and made available for the mining and online processing. Warehouse data have a subset of data called Data Marts.

25. How to fetch common records from two tables?

Common records result set can be achieved by -.

Select studentID from student. <strong>INTERSECT </strong> Select StudentID from Exam