Subqueries, Backups, Users and Privileges

Introduction ▪ SQL allows queries within queries, or subqueries, which are SELECT statements inside SELECT statements. ▪ As with a normal SELECT statement, a subquery can contain joins, WHERE clauses, HAVING clauses, and GROUP BY clauses. ▪ Subqueries are particularly powerful when coupled with SQL operators such as IN, ANY, SOME, and ALL. ▪ Subqueries can be very useful however, they can consume a lot of processing, disk, and memory resources.

Terminology ▪ Subqueries consists of Outer Queries and Inner Queries. ▪ The outer query is the main SELECT statement, and you could say that so far all of your SELECT statements have been outer queries. ▪ Shown below is a standard query: ▪ SELECT MemberId FROM Members; ▪ Using the standard query, you can nest—that is, place inside the outer query—a subquery, which is termed the inner query: SELECT MemberId FROM MemberDetails WHERE MemberId = (SELECT MAX(FilmId) FROM Films); ▪ WHERE clause is added to the outer query, and it specifies that MemberId must equal the value returned by the nested inner query, which is contained within brackets

Nest Subquery ▪ It is also possible to nest a subquery inside the inner query ▪ Consider the following example: SELECT MemberId FROM MemberDetails WHERE MemberId = (SELECT MAX(FilmId) FROM Films WHERE FilmId IN (SELECT LocationId FROM Location)); ▪ In the preceding example, a subquery is added to the WHERE clause of the inner query. ▪ A subquery inside a subquery is referred to as the innermost query

Subqueries in a SELECT list ▪ You can include a subquery as one of the expressions returning a value in a SELECT query, just as you can include a single column. ▪ However, the subquery must return just one record in one expression, in what is known as a scalar subquery. ▪ The subquery must also be enclosed in brackets.

Example SELECT Category, (SELECT MAX(DVDPrice) FROM Films WHERE Films.CategoryId = Category.CategoryId), CategoryId FROM Category;

Explaination ▪ The SELECT query starts off by selecting the Category column, much as you’ve already seen many times before. ▪ However, the next item in the list is not another column but rather a subquery. ▪ This query inside the main query returns the maximum price of a DVD. ▪ An aggregate function returns only one value, complying with the need for a subquery in a SELECT statement to be a scalar subquery. ▪ The subquery is also linked to the outer SELECT query using a WHERE clause. ▪ Because of this link, MAX(DVDPrice) returns the maximum price for each category in the Category table ▪ Starting with the first row in the results, the Category is Thriller and the CategoryId is 1 ▪ The subquery is joined to the outer query by the CategoryId column present in both the Films and Category tables ▪ For the first row, the CategoryId is 1, so the subquery finds the maximum DVDPrice for all films in the Films table where the CategoryId is 1 ▪ Moving to the next row in the outer query, the Category is Romance and the CategoryId is 2. This time, the subquery finds the maximum DVDPrice for all records where the CategoryId is 2 ▪ The process continues for every row in the Category table.

Subquery returning more than 1 value ▪ If, however, you change the MemberDetails table to the Attendance table, where MemberId appears more than once, you get the following query: SELECT FilmName, PlotSummary, (SELECT MeetingDate FROM Attendance WHERE MemberId = 1) FROM Films; ▪ Executing this query results in an error message similar to the following: “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”

Operators – IN, ANY, ALL, SOME, EXISTS ▪ So far, all the subqueries we have discussed have been scalar subqueries i.e. , queries that only return only one row. ▪ If more than one row is returned, you end up with an error. ▪ Next, we’ll discuss about operators that allow you to make comparisons against a multi-record results set

IN Operator ▪ The IN operator allows you to specify that you want to match one item from any of those in a list of items. ▪ For example, the following SQL finds all the members born in 1967, 1992, or 1937: SELECT FirstName, LastName, YEAR(DateOfBirth) FROM MemberDetails WHERE YEAR(DateOfBirth) IN (1967, 1992, 1937);

IN – in subquery ▪ Instead of providing a list of literal values, a SELECT query provides the list of values. For example, if you want to know which members were born in the same year that a film in the Films table was released, you'd use the following SQL query SELECT FirstName, LastName, YEAR(DateOfBirth) FROM MemberDetails WHERE YEAR(DateOfBirth) IN (SELECT YearReleased FROM Films); ▪ Results: The subquery (SELECT YearReleased FROM Films) returns a list of years from the Films table. If a member's year of birth matches one of the items in that list, then the WHERE clause is true and the record is included in the final results

Joins Vs Subquery ▪ An INNER JOIN coupled with a GROUP BY statement could be used instead, as shown below: SELECT FirstName, LastName, YEAR(DateOfBirth) FROM MemberDetails JOIN Films ON YEAR(DateOfBirth) = YearReleased GROUP BY FirstName, LastName, YEAR(DateOfBirth); ▪ Running this query gives the same results as the previous query. So which is best? ▪ Unfortunately, there’s no definitive answer; very much depends on the circumstances, the data involved, and the database system involved ▪ A lot of SQL programmers prefer a join to a subquery and believe that to be the most efficient! ▪ However, if you compare the speed of the two using MS SQL Server 2000, in this case, on that system, the subquery is faster by roughly 15 percent. ▪ Given how few rows there are in the database, the difference was negligible in this example, but it might be significant with a lot of records. ▪ Which way should you go? ▪ You should go with the way that you find easiest, and fine-tune your SQL code only if problems occur during testing. ▪ If you find on a test system with a million records that your SQL runs like an arthritic snail with heavy shopping, then you should go back and see whether you can improve your query

ANY and SOME Operators ▪ First, ANY and SOME are identical; they do the same thing but have a different name. ▪ The examples refer to the ANY operator, but you can use the SOME operator without it making a bit of difference. ▪ For ANY to return true to a match, the value being compared needs to match any one of the values returned by the subquery. ▪ You must place the comparison operator before the ANY keyword. ▪ For example, the following SQL uses the equality (=) operator to find out if any members have the same birth year as the release date of a film in the Films table: SELECT FirstName, LastName, YEAR(DateOfBirth) FROM MemberDetails WHERE YEAR(DateOfBirth) =ANY (SELECT YearReleased FROM Films); ▪ The WHERE clause specifies that YEAR(DateOfBirth) must equal any one of the values returned by the subquery (SELECT YearReleased FROM Films)

ALL ▪ The ALL operator requires that every item in the list (all the results of a subquery) comply with the condition set by the comparison operator used with ALL. For example, if a subquery returns 3, 9, and 15, then the following WHERE clause would evaluate to true because 2 is less than all the numbers in the list: WHERE 2 < ALL (3,9,15) ▪ However, the following WHERE clause would evaluate to false because 7 is not less than all of the numbers in the list: WHERE 7 < ALL (3,9,15) ▪ This is just an example, though, and you can’t use ALL with literal numbers, only with a subquery.

Exists ▪ The EXISTS operator is unusual in that it checks for rows and does not compare columns. So far you’ve seen lots of clauses that compare one column to another. On the other hand, EXISTS simply checks to see whether a subquery has returned one or more rows. If it has, then the clause returns true; if not, then it returns false. SELECT City FROM Location WHERE EXISTS (SELECT * FROM MemberDetails WHERE MemberId < 5); ▪ The inner subquery provides a results set with three rows. Therefore, EXISTS evaluates to true, and you get the following results:

HAVING - Clause ▪ The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. ▪ SQL HAVING Syntax SELECT column name, aggregate function(column name) FROM table name WHERE column name operator value GROUP BY column name HAVING aggregate_function(column_name) operator value RESULT ----- Now we want to find if any of the customers have a total order of less than 2000. We use the following SQL statement: SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000

What is Backup ? ▪ Backups are one of the most important parts of database maintenance ▪ Backing up files can protect against accidental loss of user data, database corruption, hardware failures, and even natural disasters. ▪ Unfortunately they are also sometimes the most neglected part of a larger software effort ▪ No database design can protect against system crashes, power failures, and the user accidentally deleting critical information ▪ Without good backups, you could lose significant chunks of data

Why Backup ? ▪ Because data is the heart of the enterprise, it's crucial for you to protect it. And to protect your organization's data, you need to implement a data backup and recovery plan. ▪ That includes normal damage caused by logical disasters such as power glitches, viruses, spilled soda, and EBCAK (Error Between Chair and Keyboard) problems, but it also includes physical calamities such as fire, tornado, and volcanic eruption ▪ Backups is no good if they’re stored next to the database’s computer and it is hit by one of these; – A meteor that reduces the whole thing to a pile of steel and plastic splinters; Earthquake that destroy storage facility; Flood; Tornado; Volcano Eruption ▪ To avoid these kind of problems, think about taking backups offsite ▪ Of course, that creates a potential security issue if data is sensitive (for example, credit card numbers, medical records, or salary information)

Types of Backups ▪ Normal/full backups: All files that have been selected are backed up, regardless of the setting of the archive attribute. When a file is backed up, the archive attribute is cleared. If the file is later modified, this attribute is set, which indicates that the file needs to be backed up. ▪ Copy backups: All files that have been selected are backed up, regardless of the setting of the archive attribute. Unlike a normal backup, the archive attribute on files isn't modified. This allows you to perform other types of backups on the files at a later date. ▪ Daily backups: Designed to back up files using the modification date on the file itself. If a file has been modified on the same day as the backup, the file will be backed up. This technique doesn't change the archive attributes of files.

Types of Backup… Cont’d… ▪ Incremental backups: Designed to create backups of files that have changed since the most recent normal or incremental backup. The presence of the archive attribute indicates that the file has been modified and only files with this attribute are backed up. When a file is backed up, the archive attribute is cleared. If the file is later modified, this attribute is set, which indicates that the file needs to be backed up ▪ Differential backups: Designed to create backup copies of files that have changed since the last normal backup. The presence of the archive attribute indicates that the file has been modified and only files with this attribute are backed up. However, the archive attribute on files isn't modified. This allows you to perform other types of backups on the files at a later date.

Local vs Remote Backups ▪ A local backup is performed on the same host where the MySQL server runs, whereas a remote backup is done from a different host ▪ For some types of backups, the backup can be initiated from a remote host even if the output is written locally on the server host ▪ Physical backup methods typically are initiated locally on the MySQL server host so that the server can be taken offline, although the destination for copied files might be remote ▪ mysqldump can connect to local or remote servers ▪ mysqlhotcopy performs only local backups: It connects to the server to lock it against data modifications and then copies local table files ▪ SELECT ... INTO OUTFILE can be initiated from a local or remote client host, but the output file is created on the server host

Incremental Backup ▪ Making an incremental backup is faster than making a full backup but restoring the data is harder – Because they are faster, incremental backups are useful for really big databases where it would take too long to make a full backup ▪ For example, suppose you have a really active database that records many thousands of transactions per day, such as a database that tracks keywords used in major news stories around the world – Suppose that you need the database to be running at full speed 20 hours a day on weekdays but a full backup takes 12 hours; Then on Saturday morning you might make a full backup and on other days you would make an incremental backup – Now suppose the database crashes and burns on a Thursday – To restore the database, you would restore the previous weekend’s full backup and then apply the incremental backups for Monday, Tuesday, and Wednesday that could take quite a while

Full Backup ▪ A full backup includes all data managed by a MySQL server at a given point in time ▪ Full backup makes a copy of everything in the database – Depending on the size of the database, this might take a lot of time and disk space – For a reasonably small database that is only used during business hours, you might have the computer automatically back up the database at midnight – Even if the backup takes several hours, the computer has little else to do in the middle of the night

How to take Backup while in use ▪ Some DBs allow backup processing while in use ▪ This is critical for databases that must be available most or all of the time ▪ The backup will slow the database down so you still need to schedule backups for off-peak periods such as weekends or the middle of the night, but at least the database can keep running ▪ For example, a local grocery store’s cash registers perform downloads, uploads, and backups in the middle of the night ▪ If you stop in around midnight, the self-checkout machines usually run much slower than they do during the day.

Faster Backup ▪ To make the process a bit faster, you could make a larger incremental backup halfway through the week ▪ On Monday, Tuesday, Thursday, and Friday, you would make an incremental backup recording changes since the previous day ▪ On Wednesday you would make an incremental backup to record all changes made since the previous Saturday full backup ▪ Now to recover from a crash on Thursday, you only need to restore Saturday’s full backup and then Wednesday’s incremental backup ▪ It will still take a while but it will be a bit faster and easier ▪ Wednesday’s incremental backup will also take longer than the daily backups but it will be a lot faster than a full backup

Database Password Protection ▪ Passwords are the most obvious form of security in most applications ▪ Different databases handle passwords differently and with different levels of safety ▪ Types of Passwords – Single Password DB – Individual Passwords – Operating System Passwords

Individual Password ▪ More sophisticated databases give each user a separate password and that has several advantages over a single password database ▪ If the database logs activity, you can tell who logged into the database when ▪ If there are problems, the log may help you narrow down who caused the problem and when ▪ If the database logs every interaction with the database (or if your application does), you can tell exactly who messed up ▪ Another advantage to individual passwords is that the user interface program doesn’t ever need to store a password

Operating System Password ▪ Some databases don’t manage passwords very well ▪ They may use little or no encryption, may not enforce any password standards (allowing weak passwords such as “12345” and “password”), and may even write passwords into log files where a hacker can find them relatively easily ▪ If your database can integrate its own security with the security provided by the operating system, make it do so ▪ In any case, take advantage of the operating system’s security ▪ Make sure users pick good operating system passwords and don’t share them ▪ A hacker won’t get a chance to attack your database if he can’t even log in to the operating system

Single Password DB ▪ At the weaker end of the spectrum, some DBs provide only a single password for the entire DB ▪ The single password provides access to the entire database ▪ That means a bad guy who learns the password can get into the database ▪ It also means anyone who should use the DB must share that password ▪ One consequence of that is that you cannot easily tell which user makes which changes to the data ▪ In practice that often means the program that provides a user interface to the database knows the password and then it may provide its own extra layer of password protection

Good Password Rules ▪ Picking good passwords is something of an art ▪ You need to pick something obscure enough that an evil hacker (or your prankster coworkers) can’t guess but that’s also easy enough for you to remember ▪ It’s easy to become overloaded when you’re expected to remember the database password in addition to your computer user name and password, bank PIN number, voice mail password, online banking password, PayPal password, eBay password, locker combination, anniversary, and children’s names ▪ And you don’t want to use the same password for all of these because then if someone ever steals your eBay password, they know all of your passwords

Good Password Rules… ▪ Many companies have policies that require you to use certain characters in your password (must include letters, numbers, and a special character such as $ or #, and you need to type every other character with your left hand and your eyes crossed) ▪ So what do users do when faced with dozens of passwords that must pass complex checks? – They write their passwords down where they are easy to find – They pick sequential passwords such as Secret1, Secret2, and so forth – They use names and dates that are easy to remember and guess. ▪ By throwing names, dates, and common words at the DB, it would be easy to guess passwords

Users ▪ To enable the user to connect with no password, include no IDENTIFIED BY clause: CREATE USER 'jeffrey'@'localhost'; ▪ To assign a password, use IDENTIFIED BY with the literal cleartext password value: CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'; Drop User: DROP USER 'jeffrey'@'localhost';

What are Privileges ? ▪ Most relational databases allow you to restrict each user’s access to specific tables and even columns within a table ▪ Typically you would define groups such as Clerks or Managers and then grant permission for users in those groups to view certain data. You may also be able to grant exceptions for individual users ▪ For example, suppose your Employees table contains columns holding three levels of data ▪ Data available to anyone includes the employee’s name, office number, phone number, and so forth ▪ Data available only to managers includes the employee’s salary and performance reviews ▪ Data available to human resources includes the employee’s next of kin, insurance information, school grades, and beneficiary name ▪ You could get into serious trouble if some of that data were to slip out

GRANT and REVOKE ▪ SQL GRANT and REVOKE statements let you give and withdraw privileges ▪ It is generally safest & most recommended to give users the fewest privileges possible to do their jobs ▪ Then if an application contains a bug and accidentally tries to do something stupid, such as dropping a table or showing the user sensitive information, the database won’t allow it ▪ Rather than remembering to remove every extraneous privilege from a new user, many database administrators revoke all privileges and then grant those that are needed ▪ That way the administrator cannot forget to remove some critical privilege

Grant Syntax For Database: ▪ GRANT ALL ON mydb.* TO 'someuser'@'somehost‘; ▪ GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost'; For Tables: ▪ GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost'; ▪ GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost'; For Columns: ▪ GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost'; For Stored Routines: ▪ GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost'; ▪ GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

REVOKE Syntax ▪ REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ... ▪ REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ... Example: ▪ REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; ▪ REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

Thank You ! Presented By Tanmay and Ashwin

Subqueries, Backups, Users and Privileges

More Related Content

What's hot

Viewers also liked

Similar to Subqueries, Backups, Users and Privileges

Subqueries, Backups, Users and Privileges