INTRODUCTION TO SQL 2

INTRODUCTION TO SQL 2

• 1. Slide1 The Where command allows you to filter the entries to be listed. When we do not use Where the specified fields of all records in the table will be listed. If we specify one or more conditions after the Where command, only records that meet these conditions will be listed. INTRODUCTION TO SQL 2 Where to use
• 2. Slide2 Using As Command When records are listed, the column headings will look the same as the header in the database. This user does not change the column headings in the table, it just makes it look different when listing. Search with Like The fields of the text type are used to search for expression. Search can be customized by typing special expressions such as *% before or after the search phrase. When the * expression is valid for Access, it is necessary to use the % sign in SQL Server. When using double quotes in Access, fixed texts are specified using single quotes in SQL Server.
• 3. Slide3 In Command Usage If there is a possibility that a field in the table may be equal to more than one value, we can specify these conditions with the In command instead of using Or. If the specified field is equal to one of the values written in the parentheses, that record will be listed. Using Is Null We can use is null to select records where the specified field is empty and is not null to select non-empty.
• 4. Slide4 Order By Usage Enables the sorting of records according to the data in a certain field, in ascending (ascending order - ASC) or descending order (descending order). If no asc or desc is specified, Asc is accepted by default and ascending is ordered. Top Command It is used to retrieve a certain amount of records, not all of them. For example, to list the first three students according to the exam grade.
• 5. Slide5 Distinct Usage Sometimes the same records in the table used can be passed more than once. In such cases, we can use the distinct command if we want to list the same values only once.
• 6. Slide6 SQL Comparison Operators =Equal SELECT * FROM tableName WHERE field1 = "Value1" Note: The fixed text is specified using double quotes in Access and single quotes in SQL Server. Quotes are not used in numerical comparisons. SELECT * FROM student WHERE age = 15
• 7. Slide7 <> If not equal: The != Sign can be used according to the operating platform. SELECT * FROM tabloAdi WHERE alan1 <> "Değer1" > Large SELECT * FROM staff WHERE salary > 1999 < Small SELECT * FROM mathematics WHERE exam1 < 50
• 8. Slide8 > = Greater than or equal to SELECT * FROM staff WHERE salary> = 2000 < = Less than or equal SELECT * FROM mathematics WHERE exam1 <= 49 BETWEEN ... AND ... SELECT * FROM staff WHERE salary BETWEEN 2000 AND 3000 Search is performed, including the specified numbers. In this example, records that are equal to 2000 and 3000 will also be listed.
• 9. Slide9 SQL Logical Operator If there is more than one condition that we need to specify after the Where command, we can write these terms with and or between them. And Operator Use And if all of the conditions indicated with are fulfilled, that record is listed; SELECT * FROM mathematics WHERE exam1 < 50 AND exam2 < 50
• 10. Slide10 Or Operator Use Or, if at least one of the conditions specified is met, that record is listed. SELECT * FROM mathematics WHERE exam1 < 50 OR exam2 < 50 In this example, records with an exam1 or exam2 field of less than 50 will be listed.
• 11. Slide11 If the number of conditions we need to write is more than two and some of them should be at least one of them, we should use parenthesis. The query will be processed by checking the brackets first and then the remaining parts. And Or Use SELECT * FROM math WHERE (exam1 >= 50 OR exam2 >= 50) AND exam3 >= 50 To list the record when the above query runs; -exam3 must be greater than or equal to 50 - at least one of the exam1 and exam2 fields must be greater than or equal to 50
• 12. Slide12 Grouping Data – Group By Commands The GROUP BY command allows us to group the values in the specified field to obtain the desired information for each group. For example, in the dgLocation field in a table, we can use the following query if we want to find out how many times a city has passed. SELECT dgLocation, COUNT (*) FROM Student GROUP BY dgLocation; In the example above, the records in the student table are grouped according to the dgSpace field and the number of records in each group is obtained.
• 13. Slide13 Grouping Data – Group By Commands Certain limitations may be required during the implementation of group operations. When determining group conditions, we can use the word HAVING to display only the groups that meet the desired conditions. The word HAVING is written after grouping. The records are grouped by Group By, then the groups that meet the condition specified by HAVING are listed. SELECT dgLocate, COUNT (*) FROM Student GROUP BY dgLocation HAVING count (dgLoc)> = 2;
• 14. Slide14 WHERE does not include rows that do not meet the requested criteria by filtering the records. Where should be used before Group By did not include rows that do not meet the requested criteria by filtering the records. Where should be used before Group By? In summary, if a Where By statement is used in a Group By query, it must be used before the Group By statement because the records must be filtered first so that the remaining records can be grouped. Having has to be written after the grouping process because it will filter the groups. Group By Command
• 15. Slide15 Group By Command Order By can be sorted using the last one. SELECT dgLocate, COUNT (*) FROM Student WHERE gender = ‘M' GROUP BY dgLocation HAVING count (dgLoc)> = 2; In the above example, it is ensured that records with gender "M" are grouped according to dgLocation field, HAVING count (dgLocation)> = 2, so that birth places less than 2 are not listed.
• 16. Slide16 Join Tables with Join Commands Enables us to list by matching the fields specified in two different tables. Two tables are given below as an example. In the mathematics table, the names of the students were not written unnecessarily and they were identified using their numbers. student information table mathematics table
• 17. Slide17 Join Tables with Join Commands Students' math grades are also listed, and since there are no student names in this table, it will not be easy to understand which student gets how many. To do this, two tables should be combined, each number in the mathematics table should be found by looking at the other table and the name and surname of that student. Here is the Inner Join command used to join the tables, ensuring the necessary match
• 18. Slide18 Join Tables Select StudentInformation.name, StudentInformation.surname, Math.Exam1 Join Mathematics Inner Join studentInformation on (Math.Number = studentInformation.Number) In the above query, the data in the two tables are listed according to the number fields. The Inner Join command specifies the tables to be merged, and the ten commands specify how to match records in different tables. The Join command can be used in 4 different ways.
• 19. Slide19 1. Inner Join It does not exclude records that are empty in the fields where we want the match to be made. Considering the following example, numbers not included in one of the tables will not be listed. That is, record 156 in table 1 will not be included in the result as it will not match the other table. Likewise, student 95 in table 2 will not be listed. Select studentInformation.name, studentInformation.surname, Math.Exam1 From Mathematics Inner Join studentInformation on (Math.Number = studentInformation.Number)
• 20. Slide20 2. Left Join (Left Outer Join) All the existing numbers in the table written to the left of the first table, left join, d They are included in the list even if they are not in the tables. In this example, student 156 will be listed in the first table and not in the other table. Select studentInformation.name, studentInformation.surname, Math.Exam1 From student information Join Left math on (Math.Number = studentInformation.Number)
• 21. Slide21 3. Right Join (Right Outer Join) It can be thought of as the opposite of the left join command. This time all entries in the table to the right will be included in the list. When the following query runs, student 95 in table 2 will be included in the listing, even if it is not in the first table. The first name and surname will remain empty because there will be no information matching it in the first table. Select studentInformation.name, studentInformation.surname, Math.Exam1 Right from math on (Math.Number = studentInformation.Number)
• 22. Slide22 4. Full Join (Full Outer Join) In both tables, records that are not in the other are included in the list. Select studentInformation.name, studentInformation.surname, Math.Exam1 From Mathematics Full Join studentInformation on (Math.Number = studentInformation.Number)
• 23. Slide23 Subqueries If a select query is to be abbreviated according to the data in another table, a query structure as follows can be used. SELECT * FROM table1 WHERE = fieldX (SELECT fieldY FROM table2 WHERE fieldY = value) Here, the inner query will first run and return a value from table2, then the outer query will run.
• 24. Slide24 Example The following query can be used to list entries whose first exam grade is higher than the grade point average in the exams table. SELECT name, surname FROM exams WHERE exams1> = (SELECT AVG (exams1) FROM exams) Example: When the following query is run; Employee numbers with salary 2000 and above are returned by the inner query, followed by the outer query, with records listed as one of these values in the staffID field. SELECT name, surname FROM staff IN THE WHERE staffID (SELECT staffID FROM fees WHERE salary> = 2000) The In, Any, and All commands are frequently used when the inner loop returns a large number of values.
• 25. Slide25 In Command The In command is used to list records that are equal to any of the specified values. In this example, it is provided to list the students whose birth place is Istanbul, Ankara or Izmir. SELECT * FROM students WHERE dgLocation IN ("Istanbul", "Ankara", "Izmir")