SQL

Just want to share with you on how to concatenate or combine one or more values from multiple rows. This SQL code is part of my “Online Hotel Reservation” project. The idea here is to group column values while concatenating the other column to avoid duplicate records. Here’s an example: I have the following record on my table: Room Type Extras Price Budget Room Extra Person 250 Economy Room Extra Person 250 Budget Room Breakfast 350 Economy Room Breakfast 350 Superior Room Extra Person 400 Then I want the following result: Room Type Extras Price Budget Room & Economy Room Extra Person 250

If you are working with SQL Server Management Studio Tools and encounter the following error when modifying one of your tables: “Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.” All you have to do is unchecked the “Prevent saving changes that require the table to be re-created” under the tools menu.

The DROP statement is used to delete object in SQL, with the DROP statement we can remove index, tables and even databases easily. DROP INDEX The indexes of the table can be dropped by the using the following command. SYNTAX for MS Access DROP INDEX index_name ON TABLE_NAME SYNTAX for SQL SERVER DROP INDEX TABLE_NAME.index_name SYNTAX for Oracle DROP INDEX index_name SYNTAX for MySQL ALTER TABLE TABLE_NAME DROP INDEX index_name For various DBMS the respective statement will drop the indexes from the table. TO DROP TABLES

The MID() function is used to extract values from a column. The MID() contain three (3) parameters. The first one is used to select which column to extract, the second one is the starting position, the last one is the number of characters to be extracted. SQL MID() Syntax SELECT MID(column_name,START[,LENGTH]) FROM TABLE_NAME Consider the following table for this exercise Users Firstname Lastname Salary DeptID John Smith 1000 1 Mathew Simon 3000 1 Bill Steve 2200 1 Amanda Rogers 1800 2 Steve Hills 2800 2 Steve jobs 2400 2 bill cosby 700 3 Example # 1

The AVG function calculates the average of a specified column. It works only on a numeric column. It first sums up all data and then divides it by the total number of rows.For example if the sum of 5 items is 10 then average is 10/5 = 2. SQL AVG() Syntax SELECT AVG(column_name) FROM TABLE Consider the following table for this exercise Users Firstname Lastname Salary deptnumber John Smith 1000 1 Mathew Simon 3000 1 Bill Steve 2200 1 Amanda Rogers 1800 2 Steve Hills 2800 2 Steve jobs 2400 2 bill cosby 700 3 Example # 1 SELECT AVG(Monthly_Salary) AS AverageSalary FROM Users

The FIRST function returns the first value of the selected column. FIRST function is not standard SQL function so it cannot be used with other DBMS like MySQL. However this is very useful with MS Access OR SQL Server. SQL FIRST() Syntax SELECT FIRST(column_name) FROM TABLE Consider the following table for this exercise Users Firstname Lastname Salary DeptID John Smith 1000 1 Mathew Simon 3000 1 Bill Steve 2200 1 Amanda Rogers 1800 2 Steve Hills 2800 2 Steve jobs 2400 2 bill cosby 700 3 Example # 1 SELECT FIRST(Firstname) AS FIRST FROM Users

The MAX function is use to return the largest value of the selected column. MAX can be use only in numeric column. As with other function, you can also give an Alias to the MAX function. SQL MIN() Syntax SELECT MAX(column_name) FROM TABLE Consider the following table for this exercise Users Firstname Lastname Salary DeptID John Smith 1000 1 Mathew Simon 3000 1 Bill Steve 2200 1 Amanda Rogers 1800 2 Steve Hills 2800 2 Steve jobs 2400 2 bill cosby 700 3 Example # 1 SELECT MAX(Salary) AS MaximumSalary FROM users

The MIN function is use to return the smallest value of the selected column. MIN can be use only in numeric column. As with other function, you can also give an Alias to the MIN function. SQL MIN() Syntax SELECT MIN(column_name) FROM TABLE Consider the following table for this exercise Users Firstname Lastname Salary DeptID John Smith 1000 1 Mathew Simon 3000 1 Bill Steve 2200 1 Amanda Rogers 1800 2 Steve Hills 2800 2 Steve jobs 2400 2 bill cosby 700 3 Example # 1 SELECT MIN(Salary) AS MinimumSalary FROM Users

The SUM function is an aggregate function use to calculate the total amount of a column. SUM function can be use only in numeric column. Syntax SELECT SUM(column_name) FROM TABLE Consider the following table for this exercise Users Firstname Lastname Salary DeptID John Smith 1000 1 Mathew Simon 3000 1 Bill Steve 2200 1 Amanda Rogers 1800 2 Steve Hills 2800 2 Steve jobs 2400 2 bill cosby 700 3 Example # 1 SELECT SUM(Monthly_Salary) AS TotalSalary FROM Users Result of the Query TotalSalary 13900 You can also add other field and using the GROUP BY Clause.