Basic SQL Questions and Answers |QualityPoint Technologies (QPT)

SQL plays an important role in the IT Industry. Learning SQL increases the Job opportunities.
People learn SQL for various reasons. For example, some people will learn it for writing SQL queries to be used in their web applications. Some learn it to become a Database Admin. And, there may be some differences based on the type of Database Server, i-e whether it is MySQL or Microsoft SQL, SQLite, etc.

This post is for telling about very basic things about SQL. So, it can be useful for anyone who is willing to learn SQL for any purpose.

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. Some DBMS examples include MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBASE, Clipper, and FoxPro.
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.
Sample Databases: School Management System, Bank Management System, Time Sheet Management System.
What are the Popular Database Management Systems in the IT Industry?
Oracle, MySQL, Microsoft SQL Server, PostgreSQL, SyBase, MongoDB, DB2, and Microsoft Access Etc...
MySQL is the widely used open source Database system which is available for Free.

What is SQL?
SQL stands for structured query language. It is a database language used for database creation, deletion, fetching rows and modifying rows etc. sometimes it is pronounced as se-qwell.
SQL is an ANSI (American National Standards Institute) standard.

When SQL appeared?
SQL appeared in 1974.
How much time it will take to learn SQL?
As far as time is concerned - in 2-3 weeks you can learn the basics and in a month's time (provided you are spending atleast 3-4 hours daily). you can learn to write basic SQL statements. In-depth SQL will need more time and practice.

What are the usages of SQL?

To create new databases
To create new tables in a database
To execute queries against a database
To retrieve data from a database
To inserts records in a database
To updates records in a database
To delete records from a database
To create views in a database

What are the applications make it very easy to install both MySQL and PhpMyAdmin?

WAMP for Windows OS
LAMP for Linux OS
MAMP for Mac OS
SAMP for Solaris OS

What is LAMP?
LAMP is an open source Web development platform that uses Linux as the operating system, Apache as the Web server, MySQL as the relational database management system and PHP as the object-oriented scripting language. (Sometimes Perl or Python is used instead of PHP.)
How to install LAMP in LINUX?
Setup the php/mysql development environment easily by installing LAMP by running below commands.

sudo apt-get install apache2 //install Apache
sudo apt-get install mysql-server // install mysql
sudo apt-get install php5 libapache2-mod-php5 //install php
sudo /etc/init.d/apache2 restart //restart server

What is WAMP?
WAMP is created by Romain Bourdon. It supports dynamic scripting languages like PHP, Python, Perl.
It is an Windows application server platform. Once WampServer is installed, you can manually add aditionals Apache, Php or MySql versions.

Windows - Operating System
Apache - Web Server
MySql - Database
PHP - Scripting Language

WampServer is available for free (under GPML license) in two distinct versions : 32 and 64 bits.
How to install WAMP in windows operating system?
Select Versions and Download WAMP server from this link.

http://www.wampserver.com/en/
User: root
Password: none

What is MAMP?
MAMP stands for Macintosh, Apache, MySQL, and PHP. MAMP is an application you can install on your Mac which allows you to have access to a local PHP server and MySQL server.
Essentially, MAMP gives you all of the tools you need to run WordPress on your machine, for development and testing purposes.
Download MAMP at https://www.mamp.info/en/downloads/
What is SAMP?
SAMP server (Solaris, Apache 2, MySQL, PHP) installation will use the following software:
Apache as delivered with the Solaris installation
MySQL 5, from Blastwave.org, using pkg-get to install it
The latest PHP 5 from php.net, downloaded and compiled
What is MySQL?
MySQL is a also a Database tool itself that uses SQL language. It is open source.
MySQL is a relational database management system. You can submit SQL queries to the MySQL database to store, retrieve, modify or delete data.
What is PL/SQL?
PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL).
It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding.
PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.
PL/SQL is a proprietary procedural language used by Oracle
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation
What are the standard SQL commands every SQL developer should know?
The basic SQL commands can be organized into the following categories:
Data Manipulation Language (DML)
Data Definition Language (DDL)
Data Control Language: (DCL)
What are various DML commands in SQL?
INSERT: Creates records.
SELECT: Retrieves records.
UPDATE: Modifies records.
DELETE: Deletes records.
What are various DDL commands in SQL?
CREATE: It creates a new table, a view of a table, or other object in database.
ALTER: It modifies an existing database object, such as a table.
DROP: It deletes an entire table, a view of a table or other object in the database.
What are various DCL commands in SQL?
GRANT: Grants privileges to users.
REVOKE: Revokes privileges previously granted to a user.

What are SQL Constraints?
Constraint in database set certain rules or property in a table.
NOT NULL: Prevents a column from having a NULL value.
DEFAULT: Specifies a default value for a column where none is specified.
PRIMARY KEY: Uniquely identifies rows/records within a database table.
FOREIGN KEY: Uniquely identifies rows/records from external database tables.
UNIQUE: Ensures all values are unique.
CHECK: Checks values within a column against certain conditions.
INDEX: Quickly creates and retrieves data from a database.
What is a query?
A Database 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.
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.
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.
What is field?
A field in database is defined as the area within a record reserved for a specific piece of data.
What is a record?
A record in the database is defined as the collection of values or fields of a specific entity.
What is the difference between VARCHAR2 AND CHAR datatypes?
VARCHAR2 represents variable length character data, whereas CHAR represents fixed length character data.
What are set operators in SQL?
Union, intersect or minus operators are called set operators.
What happens if you omit the WHERE clause in the UPDATE statement?
All the rows in the table are modified.
Can you sort a column using a column alias?
Yes. A column alias could be used in the ORDER BY clause.
What is the purpose of the MERGE statement in SQL?
The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.The MERGE statement allows conditional update or insertion of data into a database table. It performs an UPDATE if the rows exists, or an INSERT if the row does not exist.
What is a NULL value and how does it differ from a zero value?
A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.
What is the purpose of the condition operators BETWEEN and IN?
The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.
What are the ways to maintain data integrity within a RDBMS?
Entity (Row) Integrity: Avoids duplicate rows in tables.
Domain (Column) Integrity: Restricts the type, format, or range of values to enforce valid entries.
Referential Integrity: Ensures rows used by other records cannot be deleted.
User-Defined Integrity: Enforces rules set by the user that do not fall into the other categories.
What are the case manipulation functions of SQL?
LOWER, UPPER, INITCAP
What is SQL Join?
An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each.
What are the types of joins in SQL?
ANSI-standard SQL specifies five types of JOIN:

INNER,
LEFT OUTER,
RIGHT OUTER,
FULL OUTER and
CROSS

As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.

What is SQL Injection?
SQL Injection is a technique in which malicious users(Hackers or Attacker) inserts SQL command(malicious or unwanted SQL commands).
How do you insert null values in a column while inserting data?
Null values can be inserted into a table by one of the following ways −

Implicitly by omitting the column from the column list.
Explicitly by specifying the NULL keyword in the VALUES clause.

Can you remove rows from a table based on values from another table?
Yes, subqueries can be used to remove rows from a table based on values from another table.
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.
What is a Relationship and list their types?
The relationship is defined as the connection between the tables in a database. Types of relationships are

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

What is the Purpose of the Distinct Clause in SQL?
Distinct Clause allows you to display unique from the result set. This can be used with only select statements.
What is SQL* Plus?
SQL* Plus is a command line tool proprietary to Oracle. You can send SQL queries to the server using the tool. It can also help you format the result of a query.
What is T-SQL?
T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query Language (SQL).
What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can defined a start and increment value of identity column.
What is Key in SQL?
key is a set of column(s) that is used to uniquely identify the record in a table.
It is used to fetch or retrieve records / data-rows from data table according to the condition/requirement.
Keys are also used to generate relationship among different database tables or views.
What are the keys are available in SQL?
SQL Server supports various types of keys, which are listed below:

Candidate Key
Primary Key
Unique Key
Alternate Key
Composite Key
Super Key
Foreign Key

What is meant by NoSQL?
A NoSQL (originally referring to "non SQL" or "non relational") database provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational databases.
NoSQL databases are increasingly used in big data and real-time web applications.
What is a difference between SQL and iSQL*Plus?
SQL Is a Language. iSQL*Plus Is an Environment.
In SQL, Character and date columns heading are left-justified and number column headings are right-justified. In iSQL*Plus, Default heading justification is in Centre.
In SQL, Use Functions to perform some formatting. In iSQL*Plus, Use commands to format data.
What is SELECT statement?
The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.
What is the difference between primary key and unique key?
Primary should not allow null; where as unique key will allow nulls.
By default Primary key is created as clustered index; whereas unique key is created as non clustered index.
What is the difference between group by and order by?
Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.
Can Primary key is a Foreign Key on the same table?
Yes, Primary key is a Foreign Key on the same table.
What are two methods of retrieving SQL?
The two methods of retrieving SQL are

Select
Using Cursor

What is a event handler in sql?
An event handler is a routine that is written to respond to a particular event.
Who should learn SQL?

Database Developers
Database Testers
Database Administrators

What is locking in SQL? Describe its types?
Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are:

Implicit Locking: Occurs for all SQL statements except SELECT.
Explicit Locking: Can be done by user manually.

Further there are two locking methods:

Exclusive: Locks out other users
Share: Allows other users to access

What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server.
You can capture and save data about each event to a file or SQL Server table to analyze later.
For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
What are the common performance issues in SQL Server?
Following are the common performance issues: