Category: sql

SQL – Rename Database Table of content The ALTER DATABASE…MODIFY Statement Rename Database using Dump and Reimport Rename Database in SQL using RENAME DATABASE…TO (obsoleted) ”; Previous Next There can be several reasons to rename a database name. One of the reasons could be to avoid naming conflicts or to separate different types of data into different databases. Another reason can be to arrange them in an organized way which makes them more descriptive and easier to manage. The ALTER DATABASE…MODIFY Statement The ALTER DATABASE…MODIFY statement in SQL is used to rename the name of an existing database name in SQL Database Server. Please note that this command does not work in MySQL database. Syntax Following is the syntax of the ALTER DATABASE…MODIFY command − ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName; Example Following is the SQL command in SQL Server to rename the database testDB to tutorialsDB: ALTER DATABASE testDB MODIFY NAME = tutorialsDB ; Rename Database using Dump and Reimport If you are willing to rename a database name in MySQL, then simple way is to dump the complete database in an SQL file and then re-import it into a new database. This is three step process which we will follow in this tutorial: Step 1 – Dump Old Database Consider you want to rename testDB database to tutorialsDB. So first we will dump it in a simple SQL file using MySQL mysqldump command. This operation will be performed from command line and will require a database user name and password, preferably admin privilege. $ mysqldump -u username -p”password” -R testDB > testDB.sql We are using the -p flag immediately followed by our password to connect to the database with no space between. The -R is required to tell mysqldump to copy stored procedures and functions along with the normal data from the database. Step 2 – Create New Database Next step is to create new database using mysqladmin prompt command as follows: $ mysqladmin -u username -p”password” create tutorialsDB; Step 3 – Import Old Database The final step is to import old database into new database as follwing: $ mysql -u username -p”password” tutorialsDB < testDB.sql; Step 4 – Verification (Optional) Now you can verify the changes by listing down all the available databases: SHOW DATABASES; Output The output will be displayed as − Database performance_schema information_schema mysql testDB tutorialsDB Step 5 – Verification (Optional) Once you are satisfied with your changes, you can delete your old database as follows: DROP DATABASE testDB; Rename Database in SQL using RENAME DATABASE…TO (obsoleted) SQL provides a simple RENAME DATABASE…TO statement to rename an existing database. If you want to rename a database, make sure there is no active transaction in progress otherwise the complete operation might halt once you rename the database. Note: The RENAME DATABASE…TO is obsoleted. Syntax Following is the syntax of the RENAME DATABASE…TO statement − RENAME DATABASE OldDatabaseName TO NewDatabaseName; Example Before renaming a database, let us list down all the available databases − SHOW DATABASES; The output will be displayed as − Database performance_schema information_schema mysql testDB Now, issue the following command to rename the database testDB to tutorialsDB: RENAME DATABASE testDB TO tutorialsDB; There used to be a simple RENAME DATABASE command in older versions of MySQL which was intended to rename database but RENAME DATABASE command has been removed from all newer versions to avoid security risks. Print Page Previous Next Advertisements ”;

SQL – Questions and Answers ”; Previous Next SQL Questions and Answers has been designed with a special intention of helping students and professionals preparing for various Certification Exams and Job Interviews. This section provides a useful collection of sample Interview Questions and Multiple Choice Questions (MCQs) and their answers with appropriate explanations. Sr.No. Question/Answers Type 1 SQL Interview Questions This section provides a huge collection of SQL Interview Questions with their answers hidden in a box to challenge you to have a go at them before discovering the correct answer. 2 SQL Online Quiz This section provides a great collection of SQL Multiple Choice Questions (MCQs) on a single page along with their correct answers and explanation. If you select the right option, it turns green; else red. 3 SQL Online Test If you are preparing to appear for a Java and SQL related certification exam, then this section is a must for you. This section simulates a real online test along with a given timer which challenges you to complete the test within a given time-frame. Finally you can check your overall test score and how you fared among millions of other candidates who attended this online test. 4 SQL Mock Test This section provides various mock tests that you can download at your local machine and solve offline. Every mock test is supplied with a mock test key to let you verify the final score and grade yourself. Print Page Previous Next Advertisements ”;

SQL – Useful Functions ”; Previous Next SQL has many built-in functions for performing processing on string or numeric data. Following is the list of all useful SQL built-in functions − SQL COUNT Function – The SQL COUNT aggregate function is used to count the number of rows in a database table. SQL AVG Function – The SQL AVG aggregate function selects the average value for certain table column. SQL SUM Function – The SQL SUM aggregate function allows selecting the total for a numeric column. SQL SQRT Functions – This is used to generate a square root of a given number. SQL RAND Function – This is used to generate a random number using SQL command. SQL CONCAT Function – This is used to concatenate any string inside any SQL command. SQL Numeric Functions – Complete list of SQL functions required to manipulate numbers in SQL. SQL String Functions – Complete list of SQL functions required to manipulate strings in SQL. Print Page Previous Next Advertisements ”;

SQL – CREATE Database Table of content CREATE Database Statement List Databases using SQL Use/Select Databases using SQL ”; Previous Next A database is a structured collection of data that is stored in a computer system. They are used to store and retrieve the data efficiently. Databases can be created using different query languages, and SQL is one such language. CREATE Database Statement The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in SQL. If you are creating your database on Linux or Unix, then database names are case-sensitive, even though SQL keywords are case-insensitive. If you are working on Windows then this restriction does not apply. Syntax Following is the syntax to create a database in SQL − CREATE DATABASE DatabaseName; Here, the DatabaseName is the name of the database that we want to create. The database name can contain any valid identifiers, such as number, letters, or underscores. But a DatabaseName cannot be a keyword available in SQL. While creating a database, you may encounter an error such as ERROR 1044 (42000): Access denied for user ”krishna”@”localhost” to database ”DatabaseName”, this means that you do not have the necessary privileges to create a database. To create a database, you need to have admin previleges. Example Following is an example to create a database testDB using SQL CREATE DATABASE statement − CREATE DATABASE testDB; List Databases using SQL Once the database testDB is created, you can check it in the list of databases using SQL command SHOW DATABASES;. Syntax SHOW DATABASES; Output The output will be displayed as − Database master performance_schema information_schema mysql testDB Use/Select Databases using SQL We can now set the testDB as the default database by using the USE statement in SQL. Syntax USE testDB; That”s it! we have successfully created a database in SQL. Now, we can create tables and other database objects within this new database. Print Page Previous Next Advertisements ”;

SQL – Show Databases Table of content List Databases using SQL The SHOW SCHEMAS Statement The SELECT…FROM Statement The EXEC sp_databases Statement ”; Previous Next Many a times you face a situation where you need to list down all the available databases. MySQL provides a convenient way to list down all the databases using SHOW DATABASES command where as there is no particular command in MS SQL Server to show or list the databases but, you can use the SELECT…FROM command as a work around list down available databases. List Databases using SQL The SQL SHOW DATABASES statement is used to list down all the available databases in MySQL database. You can use SHOW SCHEMAS as an alternate command for SHOW DATABASES. Syntax Following is the syntax of SQL SHOW DATABASES to list down all the available databases in MySQL − SHOW DATABASES [LIKE ”pattern” | WHERE expr] ; We can use LIKE or WHERE clause along with SHOW DATABASES to filter out a list of databases. Example Following is an example to list down all the available databases. SHOW DATABASES; The output will be displayed as follows. This output depends on the number of databases available in the system − Database performance_schema information_schema mysql testDB Following is an example to list down all the databases whose name starts with test. SHOW DATABASES LIKE ”test%”; The output will be displayed as follows − Database (test%) testDB The SHOW SCHEMAS Statement You can use the SHOW SCHEMAS statement as an alternate for the SHOW DATABASES statement. Syntax Following is the syntax of the SQL SHOW SCHEMAS statement to list down all the available databases in MySQL − SHOW SCHEMAS [LIKE ”pattern” | WHERE expr] ; We can use LIKE or WHERE clause along with SHOW SCHEMAS to filter out a list of databases. Example Following is an example to list down all the available databases. SHOW SCHEMAS; The output will be displayed as follows. This output depends on the number of databases available in the system − Database performance_schema information_schema mysql testDB Following is an example to list down all the databases whose name starts with test. SHOW SCHEMAS LIKE ”test%”; The output will be displayed as follows − Database (test%) testDB The SELECT…FROM Statement If you are working with MS SQL Server then you can use the SELECT…FROM statement to list down all the available databases as shown below. SQL> SELECT * FROM sys.databases; Output If we execute the above query, it returns a table that lists down all the databases and associated information about the databases. name database_id source_database_id owner_sid master 1 NULL 001 tempdb 2 NULL 001 model 3 NULL 001 msdb 4 NULL 001 testDB 5 NULL 001000 The EXEC sp_databases Statement If you are using MS SQL Server then you can use the following EXEC sp_databases statement to list down all the databases − SQL> EXEC sp_databases; Output This will display the same result as we got from SELECT…FROM statement. name database_id source_database_id owner_sid master 1 NULL 001 tempdb 2 NULL 001 model 3 NULL 001 msdb 4 NULL 001 testDB 5 NULL 001000 Print Page Previous Next Advertisements ”;

SQL – MIN() – MAX() function Table of content The SQL MAX() Function The SQL MIN() Function ”; Previous Next The MIN() and MAX() functions in SQL are aggregate functions. They are used to compare values in a set and, retrieve the maximum and minimum values respectively. An aggregate function is a mathematical computation that takes a range of values as input and yields a single value expression, representing the significance of the provided data. MAX() and MIN() aggregate functions are generally used in two ways: As functions, they are used with the GROUP BY clause of the SELECT statement. As expressions, they are used with a subquery and HAVING clause of SELECT statement. The SQL MAX() Function The MAX() function compares the values in a column and returns the largest value among them. Syntax Following is the syntax of SQL MAX() function − MAX(column_name); Example In the following example, we are running a query for MAX() function on a table named CUSTOMERS. The objective is to retrieve the maximum salary value from this table. First of all, let us create the CUSTOMERS table using the following query − CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) ); Now, insert values into this table using the INSERT statement as follows − INSERT INTO CUSTOMERS VALUES (1, ”Ramesh”, 32, ”Ahmedabad”, 2000.00), (2, ”Khilan”, 25, ”Delhi”, 1500.00), (3, ”Kaushik”, 23, ”Kota”, 2000.00), (4, ”Chaitali”, 25, ”Mumbai”, 6500.00), (5, ”Hardik”, 27, ”Bhopal”, 8500.00), (6, ”Komal”, 22, ”Hyderabad”, 4500.00), (7, ”Muffy”, 24, ”Indore”, 10000.00); The CUSTOMERS table will be created as − ID NAME AGE ADDRESS SALARY 1 Ramesh 32 Ahmedabad 2000.00 2 Khilan 25 Delhi 1500.00 3 Kaushik 23 Kota 2000.00 4 Chaitali 25 Mumbai 6500.00 5 Hardik 27 Bhopal 8500.00 6 Komal 22 Hyderabad 4500.00 7 Muffy 24 Indore 10000.00 Here, we are comparing the salaries of CUSTOMERS and retrieving the maximum salary using the following query − SELECT MAX(SALARY) FROM CUSTOMERS; When the above query is executed, the result is displayed as − MAX(SALARY) 10000.0000 HAVING with MAX() Function In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MAX() function along with HAVING clause. SELECT ID, NAME, SALARY FROM CUSTOMERS GROUP BY NAME, ID HAVING MAX(SALARY) < 8000; When the above query is executed, we get the details of the employees whose maximum salary is less than 8000 − ID NAME SALARY 1 Ramesh 2000.00 2 Khilan 1500.00 3 Kaushik 2000.00 4 Chaitali 6500.00 6 Komal 4500.00 MAX() Function in Subqueries In the following example, we are using the MAX() function in a subquery to retrieve the record with maximum salary, from the CUSTOMERS table. SELECT * FROM CUSTOMERS WHERE SALARY = (SELECT MAX(SALARY) FROM CUSTOMERS); When we execute the above query, we will get the following result − ID NAME AGE ADDRESS SALARY 7 Muffy 24 Indore 10000.00 MAX() Function with Strings This query retrieves the maximum value (alphabetically) among the names of customers in the CUSTOMERS table using the MAX() function − SELECT MAX(NAME) AS max_name FROM CUSTOMERS; Following is the result of the above query − max_name Ramesh Aliases with MAX() Function In the following example, we use the MAX() function to retrieve the record containing maximum age from the CUSTOMERS table. We are displaying the results as a new column with the alias “max_age”. SELECT MAX(age) AS ”max_age” FROM CUSTOMERS; Following is the output of the above query − max_age 32 The SQL MIN() Function The MIN() function compares values in a column and returns the smallest value among them. Syntax Following is the syntax of SQL MIN() function − MIN(column_name); Example In this example, we are comparing values in the SALARY column of CUSTOMERS table and displaying the minimum salary using the following query − SELECT MIN(SALARY) FROM CUSTOMERS; When the above query is executed, the result is displayed as − MIN(SALARY) 1500.0000 HAVING with MIN() Function In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MIN() function along with HAVING clause. SELECT ID, NAME, SALARY FROM CUSTOMERS GROUP BY NAME, ID HAVING MIN(SALARY) > 5000; When the above query is executed, we get the details of the maximum salary for employees whose minimum salary is more than 5000, as we can see in the table that follows − ID NAME MAX_Salary 4 Chaitali 6500.0000 5 Hardik 8500.0000 7 Muffy 10000.0000 MIN() Function in Subqueries In the following example, we are using the MIN() function in a subquery to retrieve the record with minimum salary, from the CUSTOMERS table. SELECT * FROM CUSTOMERS WHERE SALARY = (SELECT MIN(SALARY) FROM CUSTOMERS); When we execute the above query, we will get the following result − ID NAME AGE ADDRESS SALARY 2 Khilan 25 Delhi 1500.00 MIN() Function with Strings Following is the query to retrieve the minimum value (alphabetically) among the names of customers in the CUSTOMERS table using the MIN() function − SELECT MIN(NAME) AS min_first_name FROM CUSTOMERS; Following is the result of the above query − min_first_name Chaitali Aliases with MIN() Function Following is the SQL query that will fetch the minimum age from the CUSTOMERS table using the MIN() function − SELECT MIN(age) AS ”min_age” FROM CUSTOMERS; When we execute the above query, the minimum value in the age field is displayed as shown below. min_age 22 Print Page Previous Next Advertisements ”;

Discuss SQL ”; Previous Next SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language. This tutorial will give you a quick start to SQL. It covers most of the topics required for a basic understanding of SQL and to get a feel of how it works. Print Page Previous Next Advertisements ”;

SQL – Comments Table of content SQL Comments Single Line Comments Multi-Line Comments ”; Previous Next A comment is a piece of text that is used in programming languages to provide additional information. These comments are ignored by the compiler and do not affect the program”s functionality. They are not visible in the output after the execution of code. Their purpose is to make the source code easier for human to understand more clearly. SQL Comments In SQL, comments can be used to explain a particular section of a query; or to skip the execution of statement. So, whenever a line of code is marked as a comment in a program, it is not executed. There are two types of comments used in MySQL database, they are as follows − Single-line comments Multi-line comments Single Line Comments The SQL single line comments starts with two consecutive hyphens (i.e. –) and extends to the end of the line. The text after the hyphens will not be executed. Syntax Following is the syntax of SQL single line comment − — This is a single-line comment Example In the following query, we are using a single line comment to write a text − — Will fetch all the table records SELECT * from table; Example Here, we have a SELECT statement that retrieves data from a table named CUSTOMERS. Though we have an ORDER BY clause in this statement, since we have commented that part, this query just retrieves the records in the CUSTOMERS table without sorting the result − SELECT * FROM CUSTOMERS — ORDER BY NAME ASC; Example Now, we are using the single line comment to ignore the last statement − SELECT * FROM CUSTOMERS; SELECT * FROM EMPLOYEES; — SELECT * FROM ORDERS WHERE ID = 6; Multi-Line Comments The SQL multi line comments are used to comment out multiple lines or a block of SQL code. It starts with /* and ends with */. Entire text between these delimiters (/*…*/) will be ignored and considered as a comment. Syntax Following is the syntax of SQL multi line comments − /* This is a multi-line comment */ Example The following example uses multi-line comment as an explanation of the query − /*following query will fetch all the table records./* SELECT * from CUSTOMERS; Example Here, we are using the multi-line comments (/*….*/) to ignore a part of the query, making it as a comment − SELECT ID /*AGE, SALARY*/ FROM CUSTOMERS WHERE SALARY = 1500.00; Example In the following query, we are ignoring multiple statements using a multi-line comment − /*SELECT * FROM CUSTOMERS; SELECT * FROM EMPLOYEE;*/ SELECT * FROM ORDERS WHERE ID = 6; Print Page Previous Next Advertisements ”;

SQL – Cursor Functions ”; Previous Next In SQL Server, a cursor is a database object that enables us to retrieve and modify data from individual rows one at a time. Nothing more than a row pointer is what a cursor actually is. It always goes together with a SELECT statement. Typically, it consists of a set of SQL statements that iterate through each row one at a time in a set number of rows. All Cursor functions are nondeterministic. To put it another way, even with the identical set of input values, these functions do not provide the same outcomes when called. List of Functions Following is a list of scalar functions that returns information about cursors in the SQL server − Sr.No. Function & Description 1 @@CURSOR_ROWS Is used to determine the number of rows in the current cursor. 2 @@FETCH_STATUS Is used to retrieve the most recent opened cursor”s current fetch status. 3 CURSOR_STATUS Is used to determine the current cursor”s status. Print Page Previous Next Advertisements ”;

SQL – Aggregate Functions ”; Previous Next In general, aggregation is a consideration of a collection of objects that are bound together as a single entity. SQL provides a set of aggregate functions that perform operations on all the entities of the column of a table considering them as a single unit. Following are the SQL aggregate functions − Sr.No. Function & Description 1 APPROX_COUNT_DISTINCT() Returns the approximate number of rows with distinct expression values. 3 CHECKSUM_AGG() Returns the checksum value. 4 COUNT_BIG() Returns the count of the number of items or rows selected by the select statement. 6 GROUPING() Returns whether the specified column expression in a group by list is aggregate or not. 7 GROUPING_ID() Returns the level of grouping. 10 STDEV() Returns the statistical standard deviation of the fields (numerical values) in a particular column. 11 STDEVP() Returns the population standard deviation for the fields (numerical values) in a particular column. 13 VAR() Returns the statistical standard variance of the fields (numerical values) in a particular column. 14 VARP() Returns the population standard variance of the fields (numerical values) in a particular column. Print Page Previous Next Advertisements ”;