SQL introduction Questions and Answers
1. What is SQL?
SQL means (Structured Query Language), which is a computer language that enables you to work with a database. Using SQL, you can insert records, update records, and delete records. You can also create new database objects such as databases and tables. And you can drop (delete) them.
2. What are the common relational database management systems that use SQL?
The common relational database management systems that use SQL are Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system.
3. When SQL first developed?
IBM first developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become a Standard Universal Language used by most of the relational database management systems (RDBMS).
4. What are the functions of SQL?
The functions of SQL are:
• Store data.
• Modify data.
• Retrieve data.
• Modify data.
• Delete data.
• Create tables and other database objects.
• Delete data.
5. Why SQL has different flavors? Give the reason?
The SQL that can be used on each one of the major RDBMS today is in different flavors. This is due to two reasons:
1) The SQL command standard is fairly complex, and it is not practical to implement the entire standard.
2) Each database vendor needs a way to differentiate its product from others.
6. What is the role of SQL?
SQL is Vendor independent. SQL is offered by all of the leading DBMS vendors and no new database product over the last decade has been highly successful without SQL support. A SQL-based database and the programs that use it can be moved from one DBMS to another vendor DBMS with minimal conversion effort and little retraining of personnel. Database tools, such as query tools, report writers, and application generators, work with many different brands of SQL databases. The vendor independence thus provided by SQL was one of the most important reasons for its early popularity and remains an important feature today.
SQL has portability Across Computer Systems. SQL-based database products run on computer systems ranging from mainframes and midrange systems to personal computers, workstations, a wide range of specialized server computers, and even handheld devices. They operate on stand-alone computer systems, in departmental local area networks, and in enterprise-wide or Internet-wide networks. SQL-based applications that begin on single-user or departmental server systems can be moved to larger server systems as they grow. Data from corporate SQL-based databases can be extracted and downloaded into departmental or personal databases. Finally, economical personal computers can be used to prototype a SQL-based database application before moving it to an expensive multiuser system.
7. What are SQL Standards?
An official standard for SQL was initially published by the American National Standards Institute (ANSI) and the International Standards Organization (ISO) in 1986 and was expanded in 1989 and again in 1992 and 1999. SQL is also a U.S. Federal Information Processing Standard (FIPS) making it a key requirement for large government computer contracts. Over the years, other international, government, and vendor groups have pioneered the standardization of new SQL capabilities, such as call-level interfaces or object-based extensions. Many of these new initiatives have been incorporated into the ANSI/ISO standard over time. The evolving standards serve as an official stamp of approval for SQL and have speeded its market acceptance.
8. What are the advantages of SQL?
1. Well Defined Standards Exist: SQL databases use long established standard, which is being adopted by ANSI & ISO. Non-SQL databases do not adhere to any clear standard.
2. High Speed: SQL Queries can be used to retrieve large amounts of records from a database quickly and efficiently.
3. No Coding is required: Using standard SQL it is easier to manage database systems without having to write substantial amount of code.
4. Emergence of ORDBMS: Previously SQL databases were synonymous with relational database. With the emergence of Object Oriented DBMS, object storage capabilities are extended to relational databases.
9. What are the disadvantages of SQL?
1. Difficulty in Interfacing: Interfacing an SQL database is more complex than adding a few lines of code.
2. More Features Implemented in Proprietary way: Although SQL databases conform to ANSI & ISO standards, some databases go for proprietary extensions to standard SQL to ensure vendor lock-in.
10. What are the characteristics of SQL?
1. SQL is an ANSI and ISO standard computer language for creating and manipulating databases.
2. SQL allows the user to create, update, delete, and retrieve data from a database.
3. SQL is very simple and easy to learn.
4. SQL works with database programs like DB2, Oracle, MS Access, Sybase, MS SQL Server etc.
11. What Can SQL do?
• SQL can execute queries against a database.
• SQL can retrieve data from a database.
• SQL can insert records in a database.
• SQL can update records in a database.
• SQL can delete records from a database.
• SQL can create new databases.
• SQL can create new tables in a database.
• SQL can create stored procedures in a database.
• SQL can create views in a database.
• SQL can set permissions on tables, procedures, and views.
12. How to use SQL in Your Web Site?
To build a web site that shows some data from a database, you will need the following:
• An RDBMS database program (i.e. MS Access, SQL Server, MySQL).
• A server-side scripting language, like PHP or ASP.
• HTML / CSS.
13. What is RDBMS?
RDBMS stands for Relational Database Management System. It is the basis for SQL and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
14. What are the functions of DBMS that SQL is used to control?
SQL is more than a query tool, although that was its original purpose and retrieving data is still one of its most important functions. SQL is used to control all of the functions of DBMS which provides for its users, including:
• Data Definition Language. In DDL user define the structure and organization of the stored data and relationships among the stored data items.
• Data Retrieval Language. An application program to retrieve stored data from the database and use it.
• Data Manipulation Language. DML allows a user or an application program to update the database by adding new data, removing old data, and modifying previously stored data.
• Data Control Language. DCL can be used to restrict a user’s ability to retrieve, add, and modify data, protecting stored data against unauthorized access.
• Data sharing. Is used to coordinate data sharing by concurrent users, ensuring that they do not interfere with one another.
• Data integrity. It defines integrity constraints in the database, protecting it from corruption due to inconsistent updates or system failures.
15. What is a Query?
Query is a term that refers to a widely available set of SQL commands called clauses. Each clause or command performs some sort of function against the database. For instance, the create clause creates tables in database and the select clause selects rows that have been inserted into your tables.
Construction of Query begins with one of the following clauses:
16. What is SQL Query Syntax?
In SQL syntax of a query, you can freely to place line breaks and spacing where you please without affecting the code. Few instances require parentheses, including the insert statement listed below. Parentheses will also be covered during our Functions lesson. Be sure to end all query statements with a semicolon (;).
SELECT * from table_name;
17. What are the categories of SQL statements?
The basic structure in SQL is the statement. Semicolons separate multiple SQL statements.
There are 3 basic categories of SQL Statements:
• SQL-Data Statements: These are used to query and modify tables and columns.
o SELECT Statement — query tables and views in the database.
o INSERT Statement — add rows to tables.
o UPDATE Statement — modify columns in table rows.
o DELETE Statement — remove rows from tables.
• SQL-Transaction Statements: These are used to control transactions.
o COMMIT Statement — commit the current transaction.
o ROLLBACK Statement — roll back the current transaction.
• SQL-Schema Statements: These are used to maintain schema (catalog).
o CREATE TABLE Statement — create tables.
o CREATE VIEW Statement — create views.
o DROP TABLE Statement — drop tables.
o DROP VIEW Statement — drop views.
o GRANT Statement — grant privileges on tables and views to other users.
o REVOKE Statement — revoke privileges on tables and views from other users.
18. What is the SQL Language Structure?
SQL is a keyword based language. Each statement begins with a unique keyword. SQL statements consist of clauses which begin with a keyword. SQL syntax is not case sensitive.
The other lexical elements of SQL statements are:
• Names — names of database elements: tables, columns, views, users, schemas; names must begin with a letter (a – z) and may contain digits (0 – 9) and underscore (_)
• Literals — quoted strings, numeric values, date time values
• Delimiters — + – , ( ) = < > <= >= <> . * / || ? ;
Basic database objects (tables, views) can optionally be qualified by schema name. A dot — “.”, separates qualifiers:
schema-name . table-name
Column names can be qualified by table name with optional schema qualification.