<p>Welcome to the SQL DBA&nbsp; Tutorials. The intent of these tutorials is to provide in depth understanding of SQL DBA&nbsp;. In addition to free SQL DBA&nbsp; Tutorials, you can find interview questions, how to tutorials and issues and their resolutions.</p>
<h2><strong>Introduction</strong></h2>
<p>Database Administrators (<a class="zem_slink" title="Database administrator" href="http://en.wikipedia.org/wiki/Database_administrator" target="_blank" rel="wikipedia">DBAs</a>) are responsible for the design, implementation, support and maintenance of computerized databases in today's organizations. The role also includes architecting, building and scaling databases for future data growth and capacity. They are also responsible for security, performance and availability of data to users and customers. .</p>
<p align="left">The SQL Server Database system from Microsoft Corporation has gained popularity over the last years, and it is believed to be one of the few surviving relational database systems in this marketplace (the others being Oracle and DB2 from IBM).</p>
<p align="left">An important part of many DBAs&rsquo; and SQL Server administrators&rsquo; jobs is keeping databases current. Doing so involves adding, changing, and deleting data from tables. T-SQL makes these tasks easy. You just need to use INSERT, UPDATE, and DELETE statements. Before I show you how to use these statements, there are a few things you need to do before proceeding.</p>
<p>Assuming that you have the permissions needed to create databases and tables and to issue datamodification statements, you can create MyDB, MyTable, and the dummy tables with the MyDB .sql and MyTable.sql files. You can download these files by going to Download the Code hotlink icon above. After you&rsquo;ve downloaded and unzipped the 98105.zip file, open a query window in either SQL Server 2005&rsquo;s SQL Server Management Studio (SSMS) or SQL Server 2000&rsquo;s Query Analyzer and copy the code in MyDB.sql into the window. In the two FILENAME entries, replace C:\Program Files Microsoft SQL Server\MSSQL.1\MSSQL\DATA MyDB.mdf with a valid path on your server. Run the query to create MyDB. To create MyTable and the dummy tables, copy the code in MyTable.sql into the query window and execute it. Before working with data in any table, you should familiarize yourself with the table&rsquo;s layout. So, open MyTable and do so.</p>
<h3>Inserting a Single Record</h3>
<p>A basic INSERT statement that adds a single record (aka row) to a table consists of three parts and looks like INSERT INTO Part1 (Part2) VALUES (Part3) Part 1 is where you specify the target table that will hold the new record. Part2 is where you list the names of the columns in the target table for which you have data. You need to enclose the list in parentheses and use commas to separate column names. Part 3 is where you provide the data to be inserted. When you want to specify the actual data values, you use the VALUES parameter followed by those values. You need to enclose the list of data values in parentheses and separate the values with commas. When a value is character based&mdash;such as a string or a datetime value&mdash;you also need to enclose it in single quotes (' '). For example, running the following statement inserts the data values of Testable and 2007-09-22 into MyTable ObjectName and Creation- Date columns, respectively: INSERT INTO MyTable (ObjectName, CreationDate) VALUES ('TestTable', '2007-09-22') If you familiarized yourself with MyTable earlier, you probably noticed that &nbsp;specified two of the three columns in this INSERT statement. The first column is an <a class="zem_slink" title="Identity column" href="http://en.wikipedia.org/wiki/Identity_column" target="_blank" rel="wikipedia">identity column</a>, which has a special meaning. The data for that column is automatically added and its values incremented with each record being inserted. This is a powerful feature of SQL Server that you should be aware of. To view the newly inserted record, execute the code SELECT * FROM MyTable <img class=" wp-image-21594 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.1492.png" alt="Capture.1492" width="1097" height="152" /> <img class="alignnone wp-image-21595" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.1493.png" alt="Capture.1493" width="308" height="125" /></p>
<h3><strong>Inserting the Results from a SELECT Statement</strong></h3>
<p>In &ldquo;T-SQL 101, Lesson 1&rdquo; (March 2008, InstantDoc ID 97724), I showed you how to get data out of a database with a SELECT statement. You can use the INSERT statement to store the data retrieved by the SELECT statement in a table. Like the single-record INSERT statement, the INSERT statement that stores the results of a SELECT statement has three parts: the target database (Part1), the column names (Part2), and the data (Part3). However, instead of using the VALUES parameter to specify the actual data values in Part3, you use a SELECT statement that retrieves the data from another source. For example, when run against the MyDB database, the query INSERT INTO MyTable (ObjectName, CreationDate) SELECT name, crdate FROM sysobjects WHERE TYPE = 'U' ORDER BY name inserts into our new table MyTable records that contain the name and creation date of all the user-defined tables in MyDB, ordered alphabetically by their names. So, assuming you ran the single-record INSERT statement previously discussed, your results from this query should look like those in Figure 2, with two exceptions. First, the Creation Date values for the MyTable and dummy tables will contain the date and time when you ran MyTable.sql. Second, the ObjectName column will be wider. (I shortened it for space reasons.) <img class=" wp-image-21596 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.1494.png" alt="Capture.1494" width="878" height="250" /> Note that Test Table is still the first record. The ORDER BY name clause only applies to the new records that the SELECT statement is inserting. Test Table was added previously with the single-record INSERT statement. You can use any SELECT statement you like, as long as the data types of the columns listed in Part2 of the INSERT statement match those columns specified in the SELECT statement. This opens the door for capturing all sorts of data. Use your newfound knowledge with caution, however. You don&rsquo;t want to be inserting millions of records at a time on a busy server or on a server with limited disk space.</p>
<h3>Updating Data</h3>
<p>Now that you have a handle on inserting data, let&rsquo;s look at how the UPDATE statement works. A simple UPDATE statement typically consists of three parts:</p>
<div class="geshifilter">
<div class="sql geshifilter-sql">&nbsp;UPDATE Part1 SET Part2 WHERE Part3</div>
</div>
<p>Part1 is where you specify the target table. Part2 is where you specify the columns that are to be changed, along with the new data for each column. Part3 is optional but in most cases essential. This is where you specify a filter using the WHERE clause. If you don&rsquo;t specify a WHERE clause, you&rsquo;ll update every single record in the table. For example, the query</p>
<div class="geshifilter">
<div class="sql geshifilter-sql">UPDATE MyTable SET CreationDate = '2007-09-23'</div>
</div>
<p>updates the CreationDate value for each record in MyTable, as Figure 3 shows. In Figure 3, note that the CreationDate value is 2007-09-23 00:00:00.000 and not 2007-09-23 as specified in the query. Because the data type of the CreationDate column is defined as datetime and the query doesn&rsquo;t specify a time portion, SQL Server assumes you mean midnight and adds 00:00:00.000. <img class=" wp-image-21597 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.1495.png" alt="Capture.1495" width="805" height="223" /> Now let&rsquo;s use a WHERE clause to modify the ObjectName and CreationDate columns for a specific record: UPDATE MyTable SET ObjectName = 'PartyTime', CreationDate = '1999-12-31 23:00:00&rsquo; WHERE TableID = 1 only the first record (specified by WHERE TableID = 1) is updated with the new table name of PartyTime and a new creation date of 1999-12-31 23:00:00. <img class=" wp-image-21598 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.1496.png" alt="Capture.1496" width="928" height="256" /></p>
<h3>Deleting Data</h3>
<p>The command that every DBA fears will fall into the wrong hands is the DELETE statement. Although it can be misused, it serves an important role when using queries to modify data in tables. A basic DELETE statement typically consists of two parts: DELETE Part1 WHERE Part2 Part1 is where you specify the target table. Part2 is where you specify a filter using the WHERE clause. Like the WHERE clause in an UPDATE statement, the WHERE clause in a DELETE statement is optional but usually essential. If you don&rsquo;t include a WHERE clause and filter, you&rsquo;ll delete all the records in the specified table. Let&rsquo;s say you want to delete the records with a creation date older than September 22, 2007 from MyTables. The query</p>
<div class="geshifilter">
<div class="text geshifilter-text">&nbsp;DELETE MyTable WHERE CreationDate &gt; '2007-09-22'</div>
</div>
<p>will do just that,. You need to be careful when modifying data with either the DELETE or UPDATE statement. Always include a WHERE clause unless you&rsquo;re positive you want to affect all the records in a table. If you&rsquo;re unsure of how many records you&rsquo;ll be affecting, try running a SELECT statement with the same WHERE clause.</p>
<h2><strong>New Features in SQL Server 2016</strong></h2>
<p>SQL Server 2016 is the latest iteration of Microsoft&rsquo;s flagship database and analytics platform with lots of new features and improved existing features making it an intelligent database and analytics platform for a mobile first, cloud first world. In fact, my first impression of SQL Server 2016 Community Technology Preview 2 is that it is another exciting launch SQL Server product that offers plenty of new features and improved existing features that enable large organizations to deliver outstanding performance for mission-critical applications and deeper insights on the organization&rsquo;s data across on-premises and cloud.</p>
<div>
<div>SQL Server 2016 is expected to be launched in the first half of 2016. In this article, I will briefly cover the new features of SQL Server 2016.</div>
<div>
<h2><strong>Hybrid Cloud Feature</strong></h2>
<div>Unlike previous SQL Server releases, SQL Server 2016 provides powerful hybrid cloud feature called Stretch Database. This feature allows you to silently archive and store the historical data into Azure SQL Database in the cloud. Meaning, current/active table data is stored locally and historical table data is dynamically moved to Azure SQL Database. When you query the data in those table, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly, improving overall performance while reducing overall storage cost and complexity. Good thing is you can enable stretch for a table or database.</div>
<div>
<h2><strong>In-database R language support</strong></h2>
<div>Earlier this year, Microsoft acquired Revolution Analytics, a company specializing in tools and its own open-source distribution for the R programming language, used for statistical analysis. SQL Server 2016 has in-database R programming language support. By incorporating R programming language support into SQL Server 2016 product set makes advanced analytics using R more accessible while making deeper insight into data easier. For example, instead of extracting the data from SQL Server database via ODBC and then analyse it R programming language, you can now able to take your R code to the data, where it will be run inside a sandbox process within SQL Server itself. This eliminates the time and storage required to move the data, and gives you all the power of R and CRAN packages to apply to your database.
<div>
<h2><strong>PolyBase into SQL Server</strong></h2>
<div>In today&rsquo;s world of interconnected devices, such as smart phones, televisions, watches, laptops, tablets, desktops, gaming devices like Xbox and Play Station, and many more, and wide access to data obtained from various sources, such as Twitter, Facebook, LinkedIn, flat files, blogs, websites, system logs, sensors and so on, data growth is among one of the three main challenges companies are facing today. Therefore, in recent years, organizations are using Apache Hadoop for big data processing from various sources, such as blogs, sensors, social media, system logs and other devices. In previous SQL Server versions, SQL Server PDW was the only release of SQL Server that came with PolyBase that let you perform standard SQL queries over relational data and Hadoop data store (eliminating the need to understand HDFS or MapReduce). However, Microsoft now integrated PolyBase into SQL Server 2016, which means you can now perform SQL queries against non-relational Hadoop data and join it on-the-fly with their existing relational data in SQL Server, eliminating the need of having to pre-load data first into the data warehouse. For example, if your ETL process includes processing of large non-relation data stored in Azure Blob Storage or Hadoop, then you can use simply query this data with standard T-SQL as if it resides in database table. You can run analytics and BI on this data. SQL Server 2016 and PolyBase therefore can help you to build out a hybrid solution that delivers deeper insights on your data, wherever it may be located. This seamless integration between traditional relational structured data and non-relational unstructured data makes SQL Server 2016 a leading enterprise ready database and analytics platform. You can run analytics and BI on this data.
<div>
<div>SQL Server 2016 and PolyBase therefore can help you to build out a hybrid solution that delivers deeper insights on your data, wherever it may be located.</div>
<div>&nbsp;</div>
<div>These core tutorials that helps you to learn the fundamentals of the SQL DBA platform. For in-depth knowledge and practical experience explore <a href="http://tekslate.com/sql-server-dba-training/" target="_blank">Online SQL Server DBA Training</a>.</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>

SQL DBA Online Tutorials For Beginners and Admin Aspirants

SQL DBA Tutorials

sql-dba-tutorials

<p style="text-align: left;">Structured Query Language(SQL) is the standard language designed to access&nbsp;relational databases. SQL is pronounced either as the word Sequel or as the letters SQL. SQL uses simple syntax that is easy to learn and use there are five types of SQL statements given below. <strong>Query Statements</strong>: Allow you to retrieve the rows stored in the database tables. You&nbsp;write a query using the SQL SELECT statement. <strong>Data Manipulation Language (DML) Statements:</strong> Allows you to modify the contents&nbsp;of the tables. There are three DML statements.</p>
<ul>
<li>INSERT - Allows you to Insert row(s) into the Table.</li>
<li>UPDATE - Allows you to change a Row.</li>
<li>DELETE - Allows you delete the rows from the Table</li>
</ul>
<p style="text-align: left;">Data Definition Language (DDL): Allows you to define the Data structures, such as&nbsp;tables that make up a database. There are five basic types of DDL statements.</p>
<ul>
<li>CREATE &ndash; Allows you to Create Database structure.</li>
<li>&nbsp; &nbsp; &nbsp; &nbsp;Ex: Create Table &ndash; Used to create a Table in the Database.</li>
<li>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Create User &ndash; Used to create the database user.</li>
<li> ALTER &ndash; Allows you to modify a database structure.</li>
<li> DROP &ndash; Allows you to remove a database structure.</li>
<li> RENAME &ndash; Allows you to change the name of the table.</li>
<li> TRUNCATE &ndash; Allows you to delete the entire contents of the table.</li>
</ul>
<p style="text-align: left;"><strong>Transaction Control (TC) Statements:</strong> Allows you to permanently record the changes&nbsp;made to rows stored in a table or undo the changes. There are three TC Statements.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;COMMIT &ndash; Allows you to permanently record the changes made to the rows.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ROLLBACK &ndash; Allows you to undo the changes made to the rows.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SAVEPOINT &ndash; Allows you to set a SAVEPOINT to which you can rollback changes&nbsp;you made.</p>
<p style="text-align: left;"><strong>Data Control Language (DCL):</strong> Allows you to change the permissions on the database&nbsp;structures. There are two DCL statements.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GRANT &ndash; Allows you to give another user access to your database structures,&nbsp;such as tables.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;REVOKE &ndash; Allows you to prevent another user from accessing to your database&nbsp;structures, such as tables.</p>
<p><strong>Tables:</strong> Table is a database object which holds the data and contains one or more&nbsp;columns associated with its datatypes <strong>Creating a Table:</strong> we use the create table statement to create a table. The simple&nbsp;syntax for the creation of table. Syntax: CREATE Table table_name (column_name type [CONSTRAINT constraint_def DEFAULT default_exp], column_name type [CONSTRAINT constraint_def DEFAULT default_exp], column_name type [CONSTRAINT constraint_def DEFAULT default_exp]&hellip; ) [ON COMMIT {DELETE | PRESERVE} ROWS] TABLESPACE tab_space; Ex: SQL&gt; Create table hariha_0016(ename varchar2(10), Eno number unique); Table Created. <strong>Altering a Table:</strong> we can alter the table using the alter statement. The alter table&nbsp;statement perform such tasks given below.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Add, modify, or drop a column.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Add or drop a constraint.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Enable or Disable a constraint. Ex: SQL&gt; Alter table hariha_0016 Add address varchar2(10); <strong>&nbsp;Modifying a Column:</strong> Ex: SQL&gt; Alter table hariha_0016 Modify address varchar2(20); <strong>Dropping a Column:</strong> Ex: SQL&gt; Alter table hariha_0016 Drop address;</p>
<p style="text-align: left;"><strong>Renaming a Table:</strong> If we want to change the name of the table then we use this&nbsp;RENAME statement. Ex: SQL&gt; Rename table hariha_0016 To&nbsp;Hariha_0015;</p>
<p style="text-align: left;"><strong>Truncating a Table:</strong> If we want to delete all the rows existing in the table then we use the TRUNCATE Statement. Ex: SQL&gt; Truncate table hariha_0015;</p>
<p style="text-align: left;"><strong>Dropping a Table:</strong> If we want to drop the total structure along with the records existing&nbsp;in the table we use this DROP statement. Ex: SQL&gt; Drop table hariha_0015;</p>
<p style="text-align: left;"><strong>Views:</strong> A view is basically a predefined query on one or more tables. Retrieving&nbsp;information from the view is done in the same manner as retrieving from the table.</p>
<p style="text-align: left;"><strong>Creating a View:</strong> by using the create view statement we can create a view. Syntax: Create [Or Replace] View view_name [(allias_name[,allias_name&hellip;.])] AS subquery [WITH {CHECK OPTION | READ ONLY} CONSTRAINT&nbsp;Constraint_name]; Ex: SQL&gt; Create view medha_0016_view AS Select ename, eno, address from&nbsp;Hariha_0016; View Created. <strong>Performing an Insert using a View:</strong> we can also perform DML operations using the&nbsp;views. Given below is the example for that. Ex: SQL&gt; Insert into medha_0016_view(&nbsp;Ename, eno, address) Values (HARIHA, 0016, HYD); 1 Row Created.</p>
<p style="text-align: left;"><strong>Modifying a View:</strong> we can modify the view using the REPLACE. If there any view&nbsp;existing with that name then it was modified with the current one. Ex: SQL&gt; Create or Replace&nbsp;view medha_0016_view AS Select a.ename, a.eno, a.address, b.city from&nbsp;Hariha_0016 a, hariha_0011 b&nbsp;Where a.eno = b.eno;</p>
<p style="text-align: left;"><strong>Dropping a View:</strong> when want to drop the view we use this statement. Only the view&nbsp;will be dropped from the database the table was not effected. Ex: SQL&gt; Drop view hariha_0016_view;</p>
<p style="text-align: left;"><strong>Sequence:</strong> A sequence is a database item that generates a sequence of integers. We&nbsp;create the sequence using the CREATE SEQUENCE statement. Syntax: Create Sequence Sequence_name [Start with Start_num] [Increment by increment_num] [ {MAXVALUE max_num | NOMAXVALUE} ] [ {MINVALUE min_num | NOMINVALUE} ] [ {CYCLE | NOCYCLE} ] [ {ORDER | NOORDER} ];</p>
<p>Ex: SQL&gt; Create Sequence medha_seq_0016 Start with 100 Increment by 1; Sequence Created.</p>
<p style="text-align: left;"><strong>Using the Sequence:</strong> Ex: SQL&gt; Select medha_seq_0016.currval &rdquo;Currval&rdquo;,&nbsp;medha_seq_0016.nextval &ldquo;Nextval&rdquo;&nbsp;From&nbsp;Dual; Output: &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Currval &nbsp; &nbsp; &nbsp;Nextval --------- &nbsp; &nbsp; &nbsp;---------- 101 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;101 <strong>Modifying the Sequence:</strong> If we want to modify the sequence by using the ALTER&nbsp;SEQUENCE we can do that. Ex: SQL&gt; Alter Sequence medha_seq_0016 Start with 1000 Increment by 2; Sequence Altered.</p>
<p style="text-align: left;"><strong>Dropping a Sequence:</strong> If we want to drop the sequence then we use this DROP&nbsp;STATEMENT. Ex: SQL&gt; Drop Sequence medha_seq_0016; Sequence Dropped.</p>
<p style="text-align: left;"><strong>Explain Plan:</strong> Explain plan gives the execution plan of the statement. PLAN_TABLE is&nbsp;necessary for explain plan.  &nbsp; &nbsp; &nbsp; &nbsp; If there is no PLAN_TABLE in your system then go with UTLXPLAN.SQL from the SQL Prompt. Syntax: SQL&gt; Explain plan Set statement_id = &lsquo;hariha_0016&rsquo;&nbsp;[into PLAN_TABLE ]&nbsp;for select * from scott.emp&nbsp;where empno = 7369; Plan Explained.  &nbsp; &nbsp; &nbsp;In TOAD(Tools for Oracle Application Design) write the SQL statement and press&nbsp;CTRL+E then it automatically shows the explain plan. It is the simple way to get the&nbsp;explain plan instead of writing the explain plan in SQL.</p>
<p><strong>SQL Trace: </strong>SQL Trace gives a wide range of information &amp; statistics that used to tune a&nbsp;group of SQL operations. We do the Sequel Trace at three levels. 1. SQL 2. Reports 3. Forms  Trace the Sequel Statements. How much time it was taking, how many rows it was&nbsp;fetching, all the information was given from SQL Trace. <strong>Steps for generating Trace file:</strong>  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Enable the Trace.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Run the DML statements.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Disable the Trace.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Get the Trace file.  &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Convert the Trace File to Readable Format.  The Trace file was generated with he extension .TRC. Oracle has give specified&nbsp;directory for trace files. To get the path use the query below. EX: SQL&gt; Select value from&nbsp;V$PARAMETER&nbsp;Where name = &lsquo;USER_DUMP_DEST&rsquo;;  &nbsp;To get the name of the Trace file also we have to use the Query. SQL&gt; Select c.value || &lsquo;\ORA&rsquo; || TO_CHAR(a.spid, &lsquo;FM00000&rsquo;) || &lsquo;.TRC&rsquo; From V$PROCESS a, V$SESSION b, V$PARAMETER c Where a .addr = b.paddr and b.ausid = userenv(&lsquo;sessionid&rsquo;) and c.name = &lsquo;USER_DUMP_DEST&rsquo;; <strong>TKPROF</strong>: Copy the trace file, which was generated earlier, and paste it in your custom&nbsp;directory. Syntax: CMD&gt; TKPROF d:\siri_0016.trc siri_0016.txt  The Hariha_0016.txt was created in the same drive where the Trace file was located. Ex: --Query to Print the Cumulative Salary of the Employee table order by DEPTNO SQL&gt; Select deptno, ename, sal, sum(sal) over(partition by deptno order by deptno, ename) &ldquo;CUM_SAL&rdquo; from scott.emp; <strong>Check out our Popular Trainings&nbsp;</strong> <a href="https://tekslate.com/structure-oaf//" target="_blank" rel="noopener">Structure of OAF</a> <a href="https://tekslate.com/advantages-disadvantages-oaf/">Advantages and Dis-Advantages of OAF</a> <a href="https://tekslate.com/oracle-data-guard-training" target="_blank" rel="noopener">Oracle data guard Training in Bangalore</a> <a href="https://tekslate.com/creating-first-oaf-page-project/">Creating First OAF Page in Project</a></p>

SQL Concepts

SQL CONCEPTS

sql-concepts

<p>There are many popular RDBMS available to work with. This tutorial gives a brief overview of few most popular RDBMS. This would help you to compare their basic features: <strong>&nbsp;MySQL</strong> MySQL &nbsp;is &nbsp;open &nbsp;source &nbsp;SQL &nbsp;database, &nbsp;which &nbsp;is &nbsp;developed &nbsp;by &nbsp;Swedish &nbsp;company &nbsp;MySQL &nbsp;AB. &nbsp;MySQL &nbsp;is pronounced "my ess-que-ell," in contrast with SQL, pronounced "sequel." MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X. MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user, and robust SQL database server. <strong>History:</strong></p>
<ul style="list-style-type: circle;">
<li>Development of MySQL by Michael Widenius &amp; David Axmark beginning in 1994.</li>
<li>First internal release on 23 May 1995.</li>
<li>Windows version was released on 8 January 1998 for Windows 95 and NT.</li>
<li>Version 3.23: beta from June 2000, production release January 2001.</li>
<li>Version 4.0: beta from August 2002, production release March 2003 (unions).</li>
<li>Version 4.01: beta from August 2003, Jyoti adopts MySQL for database tracking.</li>
<li>Version 4.1: beta from June 2004, production release October 2004.</li>
<li>Version 5.0: beta from March 2005, production release October 2005.</li>
<li>Sun Microsystems acquired MySQL AB on 26 February 2008.</li>
<li>Version 5.1: production release 27 November 2008.</li>
</ul>
<p><strong>Features:</strong></p>
<ul style="list-style-type: circle;">
<li>High Performance.</li>
<li>High Availability.</li>
<li>Scalability and Flexibility Run anything.</li>
<li>Robust Transactional Support.</li>
<li>Web and Data Warehouse Strengths.</li>
<li>Strong Data Protection.</li>
<li>Comprehensive Application Development.</li>
<li>Management Ease.</li>
<li>Open Source Freedom and 24 x 7 Support.</li>
<li>Lowest Total Cost of Ownership.</li>
</ul>
<p><strong>MS SQL Server</strong> MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its &nbsp;primary query languages are:</p>
<ul style="list-style-type: circle;">
<li>T-SQL.</li>
<li>ANSI SQL.</li>
</ul>
<p><strong>History:</strong></p>
<ul style="list-style-type: circle;">
<li>1987 - Sybase releases SQL Server for UNIX.</li>
<li>1988 - Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.</li>
<li>1989 - Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.</li>
<li>1990 - SQL Server 1.1 is released with support for Windows 3.0 clients.</li>
<li>Aston-Tate drops out of SQL Server development.</li>
<li>2000 - Microsoft releases SQL Server 2000.</li>
<li>2001 - Microsoft releases XML for SQL Server Web Release 1 (download).</li>
<li>2002 - Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server).</li>
<li>2002 - Microsoft releases SQLXML 3.0.</li>
<li>2005 - Microsoft releases SQL Server 2005 on November 7th, 2005.</li>
</ul>
<p><strong>Features:</strong></p>
<ul style="list-style-type: circle;">
<li>High Performance.</li>
<li>High Availability.</li>
<li>Database mirroring.</li>
<li>Database snapshots.</li>
<li>CLR integration.</li>
<li>Service Broker.</li>
<li>DDL triggers.</li>
<li>Ranking functions.</li>
<li>Row version-based isolation levels.</li>
<li>XML integration.</li>
<li>TRY...CATCH.</li>
<li>Database Mail.</li>
</ul>
<p><strong>ORACLE</strong> It &nbsp;is very large and multi-user database management system. Oracle is a relational database&nbsp; management system developed by 'Oracle Corporation'. Oracle works to efficiently manage its resource, a database of information, among the multiple clients requesting and sending data in the network. It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors. <strong>History:</strong> Oracle began in 1977 and celebrating its 32 wonderful years in the industry (from 1977 to 2009).</p>
<ul style="list-style-type: circle;">
<li>1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work.</li>
<li>1979 - Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).</li>
<li>1981 - RSI started developing tools for Oracle.</li>
<li>1982 - RSI was renamed to Oracle Corporation.</li>
<li>1983 - Oracle released version 3.0, rewritten in C language and ran on multiple platforms.</li>
<li>1984 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency etc.</li>
<li>1985 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency etc.</li>
<li>2007 - Oracle has released Oracle11g. The new version focused on better partitioning, easy migration etc.</li>
</ul>
<p><strong>Features:</strong></p>
<ul style="list-style-type: circle;">
<li>Concurrency</li>
<li>Read Consistency</li>
<li>Locking Mechanisms</li>
<li>Quiesce Database</li>
<li>Portability</li>
<li>Self managing database</li>
<li>SQL*Plus</li>
<li>ASM</li>
<li>Scheduler</li>
<li>Resource Manager</li>
<li>Data Warehousing</li>
<li>Materialized views</li>
<li>Bitmap indexes</li>
<li>Table compression</li>
<li>Parallel Execution</li>
<li>Analytic SQL</li>
<li>Data mining</li>
<li>Partitioning</li>
</ul>
<p><strong>MS- ACCESS</strong> This is one of the most popular Microsoft products. Microsoft Access is entry-level database &nbsp;management software. MS Access database is not only an inexpensive but also powerful database for small-scale projects. MS Access uses the Jet database engine which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL). MS Access comes with the professional edition of MS Office package. MS Access has easy to &nbsp;use intuitive graphical interface.</p>
<ul style="list-style-type: circle;">
<li>1992 - Access version 1.0 was released.</li>
<li>1993 - Access 1.1 release to improve compatibility with include the Access Basic programming language.</li>
<li>The most significant transition was from the Access 97 to the Access 2000</li>
<li>2007 - Access 2007, a new database format was introduced ACCDB which supports complex data types such as multi valued and attachment fields.</li>
</ul>
<p><strong>Features:</strong></p>
<ul style="list-style-type: circle;">
<li>Users can create tables, queries, forms and reports, and connect them together with macros.</li>
<li>The import and export of data to many formats including Excel, Outlook, ASCII, dBase, Paradox, FoxPro, SQL Server, Oracle, ODBC, etc.</li>
<li>There is also the Jet Database format (MDB or ACCDB in Access 2007) which can contain the application and data in one file. This makes it very convenient to distribute the entire application to another user, who can run it in disconnected environments.</li>
<li>Microsoft Access offers parameterized queries. These queries and Access tables can be referenced from other programs like VB6 and .NET through DAO or ADO.</li>
<li>The desktop editions of Microsoft SQL Server can be used with Access as an alternative to the Jet Database Engine.</li>
<li>Microsoft Access is a file server-based database. Unlike client-server relational database management systems &nbsp;(RDBMS), &nbsp;Microsoft &nbsp;Access &nbsp;does &nbsp;not &nbsp;implement &nbsp;database &nbsp;triggers,&nbsp; &nbsp;stored &nbsp;procedures, &nbsp;or transaction logging.</li>
</ul>

SQL RDBMS Databases - Tekslate Tutorials and Guides

SQL RDBMS Databases

sql-rdbms-databases

<p>SQL is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax: All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;). Important point to be noted is that SQL is <strong>case insensitive </strong>which means SELECT and select have same meaning in SQL statements but MySQL make difference in table names. So if you are working with MySQL then you need to give table names as they exist in the database. <strong>SQL SELECT Statement:</strong> SELECT column1, column2....columnN &nbsp; FROM &nbsp;table_name; <strong>SQL DISTINCT Clause:</strong> SELECT DISTINCT column1, column2....columnN &nbsp;FROM &nbsp; table_name; <strong>SQL WHERE Clause:</strong> SELECT column1, column2....columnN &nbsp; FROM &nbsp; table_name &nbsp; WHERE&nbsp; CONDITION; <strong>SQL AND/OR Clause:</strong> SELECT column1, column2....columnN &nbsp; FROM &nbsp; table_name &nbsp;WHERE&nbsp; CONDITION-1 &nbsp; {AND|OR} &nbsp; CONDITION-2; <strong>SQL IN Clause:</strong> SELECT column1, column2....columnN &nbsp;FROM &nbsp;table_name &nbsp; WHERE &nbsp; column_name IN &nbsp;(val-1, val-2,...val-N); <strong>SQL BETWEEN Clause:</strong> SELECT column1, column2....columnN &nbsp;FROM &nbsp;table_name &nbsp;WHERE&nbsp; column_name BETWEEN &nbsp;val-1 &nbsp;AND &nbsp;val-2; <strong>SQL Like Clause:</strong> SELECT column1, column2....columnN &nbsp; FROM &nbsp;table_name &nbsp; WHERE&nbsp; column_name &nbsp;LIKE { PATTERN }; <strong>SQL ORDER BY Clause:</strong> SELECT column1, column2....columnN &nbsp;FROM &nbsp; table_name &nbsp;WHERE&nbsp; CONDITION &nbsp; ORDER BY column_name &nbsp;{ASC|DESC}; <strong>SQL GROUP BY Clause:</strong> SELECT SUM(column_name) &nbsp; FROM &nbsp;table_name &nbsp;WHERE&nbsp; CONDITION &nbsp;GROUP BY column_name; <strong>SQL COUNT Clause:</strong> SELECT COUNT(column_name) &nbsp; FROM &nbsp; table_name &nbsp; WHERE&nbsp; CONDITION; <strong>SQL HAVING Clause:</strong> SELECT SUM(column_name) &nbsp;FROM&nbsp;&nbsp;&nbsp; table_name &nbsp;WHERE&nbsp; CONDITION GROUP BY &nbsp;column_name &nbsp;HAVING (arithematic function condition); <strong>SQL CREATE TABLE Statement:</strong> CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype, ..... columnN datatype, PRIMARY KEY( one or more columns ) ); <strong>SQL DROP TABLE Statement:</strong> DROP TABLE table_name; <strong>SQL CREATE INDEX Statement:</strong> CREATE &nbsp;UNIQUE &nbsp;INDEX &nbsp;index_name &nbsp;ON &nbsp;table_name ( column1, column2,...columnN); <strong>SQL DROP INDEX Statement:</strong> ALTER TABLE table_name DROP INDEX index_name; <strong>SQL DESC Statement:</strong> DESC table_name; <strong>SQL TRUNCATE TABLE Statement:</strong> TRUNCATE TABLE table_name; <strong>SQL ALTER TABLE Statement:</strong> ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype}; <strong>SQL ALTER TABLE Statement (Rename):</strong> ALTER TABLE table_name RENAME TO new_table_name; <strong>SQL INSERT INTO Statement:</strong> INSERT &nbsp; INTO &nbsp; table_name &nbsp;( column1, column2....columnN) &nbsp; VALUES &nbsp; ( value1, value2....valueN); <strong>SQL UPDATE Statement:</strong> UPDATE &nbsp;table_name &nbsp;SET &nbsp;column1 = value1, &nbsp;column2 = value2....columnN=valueN &nbsp;[ WHERE&nbsp; CONDITION ]; <strong>SQL DELETE Statement:</strong> DELETE &nbsp;FROM &nbsp;table_name WHERE&nbsp; {CONDITION}; <strong>SQL CREATE DATABASE Statement:</strong> CREATE &nbsp;DATABASE &nbsp; database_name; <strong>SQL DROP DATABASE Statement:</strong> DROP DATABASE database_name; <strong>SQL USE Statement:</strong> USE DATABASE database_name; <strong>SQL COMMIT Statement:</strong> COMMIT; <strong>SQL ROLLBACK Statement:</strong> ROLLBACK;</p>

SQL Syntax - Tekslate Guides

SQL Syntax

sql-syntax

SQL Statements - Tekslate Guides and Tutorials

SQL Statements

sql-statements

SQL SORTING Results by SQL Experts

SQL SORTING Results

sql-sorting-results-sql-constraints

SQL Transactions by SQL Experts

SQL Transactions and SQL Wildcard Operators

sql-transactions-sql-wildcard-operators

<h2><strong>S</strong><strong>Q</strong><strong>L Constraints Overview</strong></h2>
<p>Constraints &nbsp;are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database. SQL Contraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table. Following are commonly used constraints available in SQL</p>
<ul style="list-style-type: circle;">
<li>NOT NULL Constraint: Ensures that a column cannot have NULL value.</li>
<li>DEFAULT Constraint : Provides a default value for a column when none is specified.</li>
<li>UNIQUE Constraint: Ensures that all values in a column are different.</li>
<li>PRIMARY Key: Uniquely identified each rows/records in a database table.</li>
<li>FOREIGN Key: Uniquely identified a rows/records in any another database table.</li>
<li>CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.</li>
<li>INDEX: Use to create and retrieve data from the database very quickly.</li>
</ul>
<h3><strong>NOT NULL Constraint in SQL</strong></h3>
<p>By default, a column can hold NULL values. If you do not want a column to have a NULL value then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NULL is not the same as no data, rather, it represents unknown data. <strong>NOT NULL Constraint Example</strong> For example, the following SQL creates a new table called CUSTOMERS and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULLs: CREATE TABLE CUSTOMERS( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL, NAME &nbsp; VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT NULL, ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2), PRIMARY KEY (ID) ); If CUSTOMERS table has already been created, then to add a NOT NULL constraint to SALARY column in Oracle and MySQL, you would write a statement similar to the following: ALTER TABLE CUSTOMERS MODIFY SALARY&nbsp; DECIMAL (18, 2) NOT NULL;</p>
<h3><strong>DEFAULT Constraint in SQL</strong></h3>
<p>The SQL DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. <strong>DEFAULT Constraint Example</strong> For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here SALARY column is set to 5000.00 by default, so in case INSERT INTPO statement does not provide a value for this column then by default this column would be set to 5000.00. CREATE TABLE CUSTOMERS( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT NULL, NAME &nbsp; &nbsp; VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL, ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (ID) ); If CUSTOMERS table has already been created, then to add a DFAULT constraint to SALARY column, you would write a statement similar to the following: ALTER TABLE CUSTOMERS SALARY&nbsp; DECIMAL (18, 2) DEFAULT 5000.00;</p>
<h3><strong>SQL Drop Default Constraint</strong></h3>
<p>To drop a DEFAULT constraint, use the following SQL: ALTER TABLE CUSTOMERS ALTER COLUMN SALARY DROP DEFAULT;</p>
<h3><strong>SQL UNIQUE Constraint</strong></h3>
<p>The &nbsp;SQL UNIQUE &nbsp;Constraint &nbsp;prevents &nbsp;two &nbsp;records &nbsp;from &nbsp;having &nbsp;identical &nbsp;values &nbsp;in &nbsp;a &nbsp;particular &nbsp;column. &nbsp;In &nbsp;the CUSTOMERS table, for example, you might want to prevent two or more people from having identical age. <strong>SQL Unique Constraint Example</strong> For example, the following SQL creates a new table called CUSTOMERS and adds five columns. &nbsp;Here AGE column is set to UNIQUE, so that you can not have two records with same age: CREATE TABLE CUSTOMERS( ID &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT NULL, NAME &nbsp; &nbsp;VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; &nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL UNIQUE, ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2), PRIMARY KEY (ID) ); If CUSTOMERS table has already been created, then to add a UNIQUE constraint to AGE column, you would write a statement similar to the following: ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL UNIQUE; You can also use following syntax, which supports naming the constraint and multiple columns as well: ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);</p>
<h3><strong>DROP a&nbsp;UNIQUE Constraint</strong></h3>
<p>To drop a UNIQUE constraint, use the following SQL: ALTER TABLE CUSTOMERS DROP CONSTRAINT myUniqueConstraint; If you are using MySQL then you can use following syntax: ALTER TABLE CUSTOMERS DROP INDEX myUniqueConstraint;</p>
<h3><strong>PRIMARY Key</strong></h3>
<p>A primary key is a field in a table which uniquely identifies the each rows/records in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a <strong>composite key</strong>. If a table has a primary key defined on any field(s) then you can not have two records having the same value of that field(s). <strong>Note: </strong>You would use these concepts while creating database tables. Create Primary Key: Here is the syntax to define ID attribute as a primary key in a CUSTOMERS table. CREATE TABLE CUSTOMERS( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT NULL, NAME &nbsp; &nbsp; &nbsp; VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT NULL, ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2), PRIMARY KEY (ID) ); To create a PRIMARY KEY constraint on the "ID" column when CUSTOMERS table already &nbsp;exists, use the following SQL syntax: ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID); <strong>NOTE: </strong>If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created). For defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax: CREATE TABLE CUSTOMERS( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL, NAME &nbsp; &nbsp;VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; &nbsp; INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL, ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2), PRIMARY KEY (ID, NAME) ); To create a PRIMARY KEY constraint on the "ID" and "NAMES" columns when CUSTOMERS table already exists, use the following SQL syntax: ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME); <strong>Delete Primary Key</strong> You can clear the primary key constraints from the table, Use Syntax: ALTER TABLE CUSTOMERS DROP PRIMARY KEY ; <strong>FOREIGN Key</strong> A foreign key is a key used to link two tables together. This is sometimes called a referencing key. Primary key field from one table and insert it into the other table where it becomes a foreign key ie. Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. <strong>The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.</strong> If a table has a primary key defined on any field(s) then you can not have two records having the same value of that field(s). <strong>Example:</strong> Consider the structure of the two tables as follows: <strong>CUSTOMERS table</strong> CREATE TABLE CUSTOMERS( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL, NAME &nbsp; VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; &nbsp; INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL, ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2), PRIMARY KEY (ID) ); <strong>ORDERS table</strong> CREATE TABLE ORDERS ( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL, DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATETIME, CUSTOMER_ID INT &nbsp;references &nbsp;CUSTOMERS(ID), AMOUNT&nbsp;&nbsp;&nbsp;&nbsp; double, PRIMARY KEY (ID) ); If ORDERS table has already been created, and the foreign key has not yet been, use the syntax for specifying a foreign key by altering a table. ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID); <strong>DROP a&nbsp;FOREIGN KEY Constraint</strong> To drop a FOREIGN KEY constraint, use the following SQL: ALTER TABLE ORDERS DROP FOREIGN KEY;</p>
<h3><strong>CHECK Constraint</strong></h3>
<p>The CHECK Constraint enables a condition to check the value being entered into a &nbsp;record. If &nbsp;the &nbsp;condition evaluates to false, the record violates the constraint and isn.t entered into the table. <strong>Example</strong>: For example, the following SQL creates a new table called CUSTOMERS and adds five columns. Here we add a CHECK with AGE column, so that you can not have any CUSTOMER below 18 years: CREATE TABLE CUSTOMERS( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NOT NULL, NAME &nbsp; &nbsp; &nbsp; VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;INT &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;NOT NULL CHECK (AGE &gt;= 18), ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2), PRIMARY KEY (ID) ); If CUSTOMERS table has already been created, then to add a CHECK constraint to AGE column, you would write a statement similar to the following: ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL CHECK (AGE &gt;= 18 ); You can also use following syntax, which supports naming the constraint and multiple columns as well: ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraint CHECK(AGE &gt;= 18); <strong>DROP a&nbsp;CHECK Constraint</strong> To drop a CHECK constraint, use the following SQL. This syntax does not work with MySQL: ALTER TABLE CUSTOMERS DROP CONSTRAINT myCheckConstraint;</p>
<h3><strong>INDEX</strong></h3>
<p>The INDEX is used to create and retrieve data from the database very quickly. Index can be created by using single or group of columns in a table. When index is created it is assigned a ROWID for each rows before it sort out the data. Proper indexes are good for performance in large databases but you need to be careful while &nbsp;creating index. Selection of fields depends on what you are using in your SQL queries. <strong>Example</strong>: For example, the following SQL creates a new table called CUSTOMERS and adds five columns: CREATE TABLE CUSTOMERS( ID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, NAME &nbsp;VARCHAR (20)&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, AGE &nbsp; &nbsp; &nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL, ADDRESS&nbsp; CHAR (25) , SALARY&nbsp;&nbsp; DECIMAL (18, 2), PRIMARY KEY (ID) ); Now you can create index on single or multiple columns using the folloiwng syntax: CREATE INDEX index_name ON table_name ( column1, column2.....); To create an INDEX on AGE column, to optimize the search on customers for a particular age, following is the SQL syntax: CREATE INDEX idx_age ON CUSTOMERS ( AGE ); <strong>DROP a&nbsp;INDEX Constraint</strong> To drop a INDEX constraint, use the following SQL: ALTER TABLE CUSTOMERS DROP INDEX idx_age; Constraints can be specified when a table is created with the CREATE TABLE statement or you can use ALTER TABLE statment to create constraints even after the table is created. <strong>Dropping Constraints</strong> Any constraint &nbsp;that &nbsp;you &nbsp;have &nbsp;defined &nbsp;can &nbsp;be &nbsp;dropped &nbsp;using &nbsp;the &nbsp;ALTER &nbsp;TABLE &nbsp;command &nbsp;with &nbsp;the &nbsp;DROP CONSTRAINT option. For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command: ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK; Some implementations may provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in Oracle, you can use the following command: ALTER TABLE EMPLOYEES DROP PRIMARY KEY; Some implementations allow you to disable constraints. Instead of permanently dropping a &nbsp;constraint from the database, you may want to temporarily disable the constraint, and then enable it later. <strong>Integrity Constraints</strong> Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. There are many types of integrity constraints that play a role in referential integrity (RI). These constraints include Primary Key, Foreign Key, Unique Constraints and other constraints mentioned above</p>

SQL Constraints by SQL Experts

SQL Constraints

sql-constraints

<h2><strong>S</strong><strong>Q</strong><strong>L Clone Tables Overview</strong></h2>
<p>There may be a situation when you need an exact copy of a table, and CREATE TABLE ... SELECT... doesn't suit your purposes because the copy must include the same indexes, default values, and so forth. If you are using MySQL RDBMS, you can handle this situation by following steps.</p>
<ul style="list-style-type: circle;">
<li>Use SHOW CREATE TABLE command to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.</li>
<li>Modify the statement to change the table name to that of the clone table and execute the statement. This way you will have exact clone table.</li>
<li>Optionally, If you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.</li>
</ul>
<h3><strong>Step by Step Instructions on Cloning Tables in SQL</strong></h3>
<p>Try out following example to create a clone table for <strong>TUTORIALS_TBL </strong>whose structure is as follows: <strong>Step 1:</strong> Get complete structure about table SQL&gt; SHOW CREATE TABLE TUTORIALS_TBL \G; *************************** 1. row *************************** Table: TUTORIALS_TBL Create Table: CREATE TABLE `TUTORIALS_TBL` ( `tutorial_id` int(11) NOT NULL auto_increment, `tutorial_title` varchar(100) NOT NULL default '', `tutorial_author` varchar(40) NOT NULL default '', `submission_date` date default NULL, PRIMARY KEY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (`tutorial_id`), UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`) ) TYPE=MyISAM 1 row in set (0.00 sec) <strong>Step 2:</strong> Rename this table and create another table SQL&gt; CREATE TABLE `CLONE_TBL` ( -&gt; `tutorial_id` int(11) NOT NULL auto_increment, -&gt; `tutorial_title` varchar(100) NOT NULL default '', -&gt; `tutorial_author` varchar(40) NOT NULL default '', -&gt; `submission_date` date default NULL, -&gt; PRIMARY KEY&nbsp; (`tutorial_id`), -&gt; UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`) -&gt; ) TYPE=MyISAM; Query OK, 0 rows affected (1.80 sec) <strong>Step 3:</strong> After executing step 2 you will a clone table in your database. If you want to copy data from old table then you can do it by using INSERT INTO... SELECT statement. SQL&gt; INSERT INTO CLONE_TBL (tutorial_id, -&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tutorial_title, -&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tutorial_author, -&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; submission_date) -&gt; SELECT tutorial_id,tutorial_title, -&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tutorial_author,submission_date, -&gt; FROM TUTORIALS_TBL; Query OK, 3 rows affected (0.07 sec) Records: 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Duplicates: 0&nbsp; Warnings: 0 Finally you will have exact clone table as you wanted to have.</p>

Step by Step process of creating SQL Clone Tables

SQL Clone Tables

sql-clone-tables

<h2><strong>S</strong><strong>Q</strong><strong>L Expressions Overview</strong></h2>
<p>An &nbsp;expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. SQL EXPRESSIONs are like formulas and they are written in query language. You can also use them to query the database for specific set of data. Syntax: Consider the basic syntax of the SELECT statement as follows: SELECT column1, column2, columnN &nbsp; FROM &nbsp;table_name &nbsp;WHERE [CONDITION|EXPRESSION]; There are different types of SQL expressions, which are mentioned below: &nbsp;</p>
<h3><strong>SQL - Boolean Expressions</strong></h3>
<p>SQL Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax: SELECT column1, column2, columnN &nbsp;FROM &nbsp;table_name &nbsp;WHERE &nbsp; SINGLE &nbsp; VALUE &nbsp; MATCHTING &nbsp; EXPRESSION; Consider CUSTOMERS table has following records: SQL&gt; SELECT * FROM CUSTOMERS; +----+----------+-----+-----------+----------+ | ID | NAME&nbsp;&nbsp;&nbsp;&nbsp; | AGE | ADDRESS&nbsp;&nbsp;&nbsp; | SALARY&nbsp;&nbsp; | +----+----------+-----+-----------+----------+ |&nbsp; 1 | Ramesh&nbsp;&nbsp; |&nbsp; 32 | Ahmedabad |&nbsp; 2000.00 | |&nbsp; 2 | Khilan&nbsp;&nbsp; |&nbsp; 25 | Delhi&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 1500.00 | |&nbsp; 3 | kaushik&nbsp; |&nbsp; 23 | Kota&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 2000.00 | |&nbsp; 4 | Chaitali |&nbsp; 25 | Mumbai&nbsp;&nbsp;&nbsp; |&nbsp; 6500.00 | |&nbsp; 5 | Hardik&nbsp;&nbsp; |&nbsp; 27 | Bhopal&nbsp;&nbsp;&nbsp; |&nbsp; 8500.00 | |&nbsp; 6 | Komal&nbsp;&nbsp;&nbsp; |&nbsp; 22 | MP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp; 4500.00 | |&nbsp; 7 | Muffy&nbsp;&nbsp;&nbsp; |&nbsp; 24 | Indore&nbsp;&nbsp;&nbsp; | 10000.00 | +----+----------+-----+-----------+----------+ 7 rows in set (0.00 sec) Here is simple examples showing usage of SQL Boolean Expressions: SQL&gt; SELECT * FROM CUSTOMERS WHERE SALARY = 10000; +----+-------+-----+---------+----------+ | ID | NAME&nbsp; | AGE | ADDRESS | SALARY&nbsp;&nbsp;&nbsp; | +----+-------+-----+---------+----------+ |&nbsp; 7 | Muffy |&nbsp; 24 | Indore&nbsp; | 10000.00 | +----+-------+-----+---------+----------+ 1 row in set (0.00 sec)</p>
<h3><strong>SQL - Numeric Expression</strong></h3>
<p>This expression is used to perform any mathematical operation in any query. Following is the syntax: SELECT numerical_expression as&nbsp;&nbsp; OPERATION_NAME &nbsp;[FROM &nbsp;table_name &nbsp;WHERE &nbsp;CONDITION] ; Here numerical_expression is used for mathematical expression or any formula. Following is a simple examples showing usage of SQL Numeric Expressions: SQL&gt; SELECT (15 + 6) AS ADDITION +----------+ | ADDITION | +----------+ |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 21 | +----------+ 1 row in set (0.00 sec) There are several built-in functions like avg(), sum(), count() etc.to perform what is known as &nbsp;aggregate data calculations against a table or a specific table column. SQL&gt; SELECT COUNT(*) AS "RECORDS" FROM CUSTOMERS; +---------+ | RECORDS | +---------+ |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7 | +---------+ 1 row in set (0.00 sec)</p>
<h3><strong>SQL - Date Expressions</strong></h3>
<p>Date Expressions return current system date and time values: SQL&gt;&nbsp; SELECT CURRENT_TIMESTAMP; +---------------------+ | Current_Timestamp&nbsp;&nbsp; | +---------------------+ | 2009-11-12 06:40:23 | +---------------------+ 1 row in set (0.00 sec) Another date expression is as follows: SQL&gt;&nbsp; SELECT&nbsp; GETDATE();; +-------------------------+ | GETDATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | +-------------------------+ | 2009-10-22 12:07:18.140 | +-------------------------+ 1 row in set (0.00 sec &nbsp; SQL&gt; select 10 / 5; +--------+ | 10 / 5 | +--------+ | 2.0000 | +--------+ 1 row in set (0.03 sec)</p>

SQL Expressions with Examples by SQL Experts

SQL Expressions

sql-expressions

<p>&nbsp; <strong>Operators:</strong> Using operators(SQL), we can add the tables for reporting. Different types in <a href="http://tekslate.com/tutorials/sas/" target="_blank" rel="noopener">SAS</a> are</p>
<ol>
<li>Union all</li>
<li>Union</li>
<li>Intersect</li>
<li>Except</li>
</ol>
<p>Ex: Data lab1; Input stno test $ units; Date = &lsquo;12jan2003&rsquo;d; Format date date9.; Cards; 100&nbsp; hr&nbsp; 78 101&nbsp; hr&nbsp; 90 100&nbsp; dbp 89 100&nbsp; sbp 156 101&nbsp; dbp 90 101&nbsp; sbp&nbsp; 178 ; Data lab2; Input stno test $ units; Date = &lsquo;13jan2003&rsquo;d; Format date date9.; Cards; 102&nbsp; hr&nbsp; 78 103&nbsp; hr&nbsp; 90 103&nbsp; dbp 89 103&nbsp; sbp 156 102&nbsp; dbp 90 102&nbsp; sbp&nbsp; 178 ; Order for reporting Ex: Proc sql; Select * from lab1; Union all Select * from lab2; Quit; &nbsp; &nbsp;</p>
<h2><strong>Union:</strong></h2>
<p>It can be used to add report in sorting order (ascending order) for reporting. Ex: Proc SQL; Select * from lab1 Union Select * from lab2; Quit;</p>
<ul>
<li>Union operator defaults takes starting variables in report as a sorting variable.</li>
</ul>
<pre style="text-align: center;">&nbsp;<em><strong>Desired to gain proficiency on SAS? Explore the blog post on <a href="http://tekslate.com/base-sas-training/" target="_blank" rel="noopener">SAS Training Online</a> to become a pro in SAS.</strong></em></pre>
<h3><strong>Intersect:</strong></h3>
<p>It can be used to report common observations from required data sets. Ex: Data ex; Input stno ad $ date :date 9.; Format date date 9; Cards; 100&nbsp;&nbsp;&nbsp; Eyedis&nbsp; 12jan2003 105&nbsp;&nbsp; Eardis&nbsp; 12jan2003 102&nbsp;&nbsp; Eardis&nbsp; 12jan2003 ; Data unex ; Input stno ad $ date :date 9.; Format date :date 9; Cards; 104&nbsp;&nbsp; comma&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12jam2003 103&nbsp;&nbsp; nervous pro 12jan2003 105&nbsp;&nbsp; Eardis&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 12jan2006 ; Proc sql; Select * from ex Intersect Select * from unex; Quit; <strong>Note:</strong> Union all report with common observation. Union report without common observation(duplicate). &nbsp;</p>
<h3><strong>Difference between duplicate and common observation:</strong></h3>
<p>Common observation means observation available in one or more data set. If we check these data sets individually then common observation is a valid observation. If we check both at a time then common observation is a invalid observation(duplicate observation). &nbsp;</p>
<h3><strong>Except:</strong></h3>
<p>It can be used to report the data from the required data set observations. Ex: Proc sql; Select * from ex Except Select * from unex; Quit; /* To report the data from the required data sets without common observations*/ Ex: Proc sql; (select * from ex Except Select * from unex Union (select * from unex Except Select * from ex); Quit; &nbsp;</p>
<h3><strong>Joins:</strong></h3>
<p>Using joins we can combine the reports based on matching variables. Different types, they are:</p>
<ol>
<li>Simple join</li>
<li>Inner join</li>
<li>Outer join</li>
<li>Natural join</li>
</ol>
<h3><strong>Simple joins: </strong></h3>
<p>Using simple joins, We can report matching observations from the required data sets. Ex: Data exadevent; Input stno exad $ exdate :date 9.; Format exdate date 9; Cards; 230&nbsp;&nbsp;&nbsp; Eyedis&nbsp; 12feb2005 456&nbsp;&nbsp; skinprb&nbsp; 15feb2005 345&nbsp;&nbsp;&nbsp; cold&nbsp;&nbsp;&nbsp; 16mar2005 ; Data unexadevent ; Input stno unexad $ unexadate :date 9.; Format unexdate date 9; Cards; 230&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Nervous&nbsp;&nbsp;&nbsp; 28feb2005 156&nbsp;&nbsp; Earddis&nbsp;&nbsp;&nbsp;&nbsp; 17mar2005 145&nbsp;&nbsp; diabetis&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18mar2005 ; Proc sql; Select * from exadevent as ex, Unexadevent as unex where Ex.stno = unex .stno; Quit; &nbsp;</p>
<h3><strong>Table alias:</strong></h3>
<p>Using this concept, we can create alternate name of the table or required application temporarily.</p>
<ul>
<li>Simple join concept can be used between the multiple tables at a time.</li>
</ul>
<h3><strong>Inner join:</strong></h3>
<p><strong>&nbsp;&nbsp; </strong>&nbsp;&nbsp;Works like a simple join, but inner join can be used between 2 tables</p>
<h3><strong>On clause:</strong></h3>
<p>It can be used instead of where clause for condition.</p>
<ul>
<li>Inner join can be activated with inner join</li>
</ul>
<p>Ex: Proc sql; Select * from exadevent as ex Inner join Unexadevent as unex on Ex.stno = unex .stno; Quit;</p>
<h3><strong>Outer join:</strong></h3>
<p><strong>&nbsp;&nbsp; </strong>3 types</p>
<ol>
<li>Left join</li>
<li>Right join</li>
<li>Full join</li>
</ol>
<p>&nbsp;</p>
<h3><strong>Left join:</strong></h3>
<p><strong>&nbsp;&nbsp; </strong>&nbsp;Reports all observation from left side table and only matching observation and conditions based right side table. Ex: Proc sql; Select * from exadevent as ex Left join Unexadevent as unex on Ex.stno = unex .stno; Quit; &nbsp;</p>
<h3><strong>Right join:</strong></h3>
<p>Reports all observation from right &nbsp;side table and only matching observation and conditions based left side table. Ex: Proc sql; Select * from exadevent as ex Right join Unexadevent as unex on Ex.stno = unex .stno; Quit; &nbsp;</p>
<h3><strong>Full join:</strong></h3>
<p><strong>&nbsp;&nbsp; </strong>Reports all observation from 2 tables match the rows. Ex: Proc sql; Select * from exadevent as ex full join Unexadevent as unex on Ex.stno = unex .stno; Quit; &nbsp;</p>
<h3><strong>Natural join:</strong></h3>
<p>We can report matching observation from required <a href="http://www.sas.com/en_us/home.html" target="_blank" rel="noopener">data set</a> without using any condition. Ex: Proc sql; Select * from exadevent Natural join Unexadevent; Quit; &nbsp;</p>
<h3><strong>Self join:</strong></h3>
<p>If we join the table internally with same table, then it is called self join. Ex: Data trt; Input stno Bsbp drug $ Asbp; Cards; 190&nbsp; 167&nbsp;&nbsp; col5mg&nbsp; 178 123&nbsp; 178&nbsp;&nbsp; col5mg&nbsp; 167 198&nbsp; 167&nbsp;&nbsp; col10mg &nbsp;146 237&nbsp; 172&nbsp;&nbsp; col10mg&nbsp; 134 ; Proc sql; Select * from trt Where Bsbp &gt; Asbp; Quit; <span style="color: #000000;"><strong>For indepth knowledge on SAS, click on below</strong></span></p>
<ul>
<li><span class="white-back"><a href="http://tekslate.com/normalization-sas/" target="_blank" rel="noopener">Normalization in SAS</a></span></li>
<li><span class="white-back"><a href="http://tekslate.com/sql-aggregations-datasetrow-colummn-operations-sas/" target="_blank" rel="noopener">SQL Aggregations &amp; DataSet(Row &amp; colummn operations) in SAS</a></span></li>
<li><span class="white-back"><a href="http://tekslate.com/working-macro-variable-sas/" target="_blank" rel="noopener">Working with Macro Variable in SAS</a></span></li>
<li><span class="white-back"><a href="http://tekslate.com/interact-with-oracle" target="_blank" rel="noopener">SAS Interact With Oracle</a></span></li>
<li><a href="http://tekslate.com/sas-interview-questions-answers" target="_blank" rel="noopener">SAS Interview Questions &amp; Answers</a></li>
</ul>