Inhouse Trainings

What is a relational database?

relational database (RDB) is a collective set of multiple data sets organized by tablesrecords and columns. RDBs establish a well-defined relationship between database tables. Tables communicate and share information, which facilitates data searchability, organization and reporting.

RDBs use Structured Query Language (SQL), which is a standard user application that provides an easy programming interface for database interaction.

Relational databases are based on the relational modelan intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points.

Benefits of Relational Databases:

  • Data Consistency
  • Commitment and Atomicity
  • Stored Procedures and Relational Databases
  • Database Locking and Concurrency

Relational Database Terms

Below are the unique terms and specific definitions that will help you understand what a RDB can do and how it works:

Row : A set of data constituting a single item.

For example, the data for a single employee (e.g. first name, last name, employee ID, hire date, work location, etc.) of a company would be displayed in a row. A row can also be called a record, an entity, or a tuple.

Column: Labels for elements of rows. A column gives context to the information contained in rows. For an employee database, the column headers could be the items listed above for employees. A column is also known as an attribute or a field.

Table: A group of rows that match the parameters set up for the table. The data in a table must all be related. An employee database may have separate tables for active employees, retired employees, and former employees. A table is also known as a relation or base revelar.

View: A set of data based on a query via the RDBMS; also known as result set or derived revelar.

Domain: The set of possible values for a given column. For example, the phone number and ZIP code columns would be numbers, while first and last names would be limited to letters.

Constraint: A narrowing of a domain. For example, the domain of the work location on a employee record would be alphanumeric, but it could be restricted to a predefined list rather than being a free-form field. The phone number field would be constrained to 10 digits.

Primary key: The unique identifier of a row in a table.

Foreign key: The unique identifier of a row in another table.

Distributed Database: A database that stores data in multiple locations, rather than on a single hard drive or server.

TYPES OF DATABASE RELATIONSHIPS

The power of a relational database is in the links and relations. By connecting rows in different tables through the use of primary and foreign keys, you can create views, reports, and other slices of information to serve your organization. There are three primary types of database relationships:

One-to-One: One row in one table is connected to one and only one row in another table. For example, a Social Security number is linked to a single employee.

One-to-Many: One row in one table is connected to zero, one, or more than one rows in another table. For example, one work location can be linked to many employees.

Many-to-Many: Zero, one, or many rows in one table are linked to zero, one, or many rows in another table. For example, multiple employees can be assigned to multiple projects.

What is a subquery in SQL?

SubQuery is a query within another query. The outer query is called as main query and inner query is called as SubQuery.

The SubQuery generally executes first, and its output is used to complete the query condition for the main or outer query. Subquery must be enclosed in parentheses.

Subqueries are on the right side of the comparison operator. You can place the Subquery in a number of SQL clausesWHERE clauseHAVING clauseFROM clause.

Subqueries can be used with SELECTUPDATEINSERTDELETE statements along with expression operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.

ORDER BY command cannot be used in a SubQueryGROUPBY command can be used to perform same function as ORDER BY command.

A Sample Syntax Example for SubQuery:

SELECT column_name

FROM table_name

WHERE column_name expression operator

( SELECT COLUMN_NAME from TABLE_NAME WHERE … );

SQL Server Online Training

What is SQL?

SQL stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. It is used to perform operations on the data present in database.

SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system.

However, the standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database.

SQL commands are divided into several different types, among them data manipulation language (DML) and data definition language (DDL) statements, transaction controls and security measures.

Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

What is PL/SQL?

What is PL/SQL?

PL/SQL stands for Procedural Language extension of SQL.

PL/SQL is a combination of SQL along with the procedural features of programming languages.

It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL language code can be stored in the client system (client-side) or in the database (server-side).

SQL GRANT REVOKE Commands

SQL GRANT REVOKE Commands

DCL commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOKE. Only Database Administrator’s or owner’s of the database object can provide/remove privileges on a database object.

SQL GRANT Command

SQL GRANT is a command used to provide access or privileges on the database objects to the users.

The Syntax for the GRANT command is:

GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

  • privilege_name is the access right or privilege granted to the user. Some of the access rights are ALL, EXECUTE, and SELECT.
  • object_name is the name of an database object like TABLE, VIEW, STORED PROC and SEQUENCE.
  • user_name is the name of the user to whom an access right is being granted.
  • user_name is the name of the user to whom an access right is being granted.
  • PUBLIC is used to grant access rights to all users.
  • ROLES are a set of privileges grouped together.
  • WITH GRANT OPTION – allows a user to grant access rights to other users.

For Example:

GRANT SELECT ON employee TO user1;

This command grants a SELECT permission on employee table to user1.

You should use the WITH GRANT option carefully because for example if you GRANT SELECT privilege on employee table to user1 using the WITH GRANT option, then user1 can GRANT SELECT privilege on employee table to another user, such as user2 etc.

Later, if you REVOKE the SELECT privilege on employee from user1, still user2 will have SELECT privilege on employee table.

SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character ‘%’.

For example: To select all the students whose name begins with ‘S’

SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE ‘S%’;

The output would be similar to:

first_name          last_name
————-          ————-
Stephen                 Fleming
Shekar                   Gowda

The above select statement searches for all the rows where the first letter of the column first_name is ‘S’ and rest of the letters in the name can be any character.

Create a Database by Script

SQL Server accepts Transact-SQL (which is an extended version of the SQL standard), so you could create the database by running the following SQL script.

USE master;
GO
CREATE DATABASE Music;
GO

To do this, open a new query by clicking New Query in the toolbar and run an SQL CREATE DATABASE statement.

Just as you can specify certain properties when creating a database via the GUI, you can include those same properties when creating a database by script. Here’s an example of specifying settings for the data and log files.

USE master ;
GO
CREATE DATABASE Music
ON
( NAME = Music_dat,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Music.mdf’,
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = Music_log,
FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\Music_log.ldf’,
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) ;
GO

What is the difference between various types of joins? For instance, there’s inner join, left join, right join, full join, cross join, natural join, equi-join, and semi-join?

Different types of joins are required depending on the type of data wrangling one needs to do. Let’s work with an example here. In this toy example, we have 2 tables-

  1. Married- This contains the name and social security number ( SSN) of the people who are married.
  2. Home Owners- This contains the name and social security number ( SSN) of the people who own a home

SSN is unique for each person and will be used as a primary key to merge two tables.

Let’s now look at the types of joins using the above 2 tables and which group they would provide the information on from both or either tables.

  1. Inner Join- If we want to find out people who are both married and home owners- Mia, Liz, Benjamin, Ava.
  2. Left Join- All married folks, whether or not home owners- John, James, Harper, Amy, Mia, Liz, Benjamin, Ava.
  3. Right Join– All home owners, whether or not married- Mia, Liz, Benjamin, Ava, Sophia, William, Jacob, Michael
  4. Full Join- Everyone from both tables- Mia, Liz, Benjamin, Ava, Sophia, William, Jacob, Michael, John, James, Harper, Amy.

left

Use keys from left frame only

right

Use keys from right frame only

outer

Use union of keys from both frames

inner

Use intersection of keys from both frames

What is the difference between SQL Server and SQL?

SQL stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. It is used to perform operations on the data present in database. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database. SQL commands are divided into several different types, among them data manipulation language (DML) and data definition language (DDL) statements, transaction controls and security measures.

Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

SQL Server is a relational database management system (RDBMS) developed by Microsoft. This product is built for the basic function of storing retrieving data as required by other applications. It can be run either on the same computer or on another across a network. It is platform dependent. It is both GUI and command based software.

SQL Server is available in various editions. Some of the editions are:

  • Enterprise
  • Standard
  • Workgroup
  • Web
  • Developer
  • Express
  • Compact
  • Datacenter
  • Business Intelligence
  • Enterprise Evaluation

So, different enterprises like web development companies, mobile app development companies, banks, outsourcing companies and many other uses RDBMS like (SQL server, Oracle) for smooth functioning of their business.

MS SQL Server – Editions

SQL Server is available in various editions. This chapter lists the multiple editions with its features.

Enterprise − This is the top-end edition with a full feature set.

Standard − This has less features than Enterprise, when there is no requirement of advanced features.

Workgroup − This is suitable for remote offices of a larger company.

Web − This is designed for web applications.

Developer − This is similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation.

Express − This is free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB.

Compact − This is free embedded database for mobile application development. The maximum size of the database is 4 GB.

Datacenter − The major change in new SQL Server 2008 R2 is Datacenter Edition. The Datacenter edition has no memory limitation and offers support for more than 25 instances.

Business Intelligence − Business Intelligence Edition is a new introduction in SQL Server 2012. This edition includes all the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for advanced availability features like AlwaysOn Availability Groups and other online operations.

Enterprise Evaluation − The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. This edition has a built-in expiry of 6 months from the time that you install it.

2005 2008 2008 R2 2012 2014
Enterprise Yes Yes Yes Yes
Standard Yes Yes Yes Yes
Developer Yes Yes Yes Yes
Workgroup Yes Yes No No
Win Compact Edition – Mobile Yes Yes Yes Yes
Enterprise Evaluation Yes Yes Yes Yes
Express Yes Yes Yes Yes
Web Yes Yes Yes
Datacenter No No
Business Intelligence Yes