2 puc cs.pptx bsbshsjshsbbsjsjshdbdbbdbdd

Hshsbsbsb

1. SELECT statement in SQL is used to retrieve data from a database. Here's the basic syntax: SELECT(What _to _select) FROM(Which _table) WHERE(Conditions _to _satisfy);

2. Selecting all data It allows you to retrieve all rows and columns from a specific table. SELECT * FROM table_name ; FOR Example: SELECT * FROM PET;

3. SELECT : This keyword is used to retrieve data from a database. * : This symbol specifies that all columns should be retrieved. Alternatively, you can specify column names separated by commas if you only want specific columns. FROM : This keyword indicates the table from which you want to retrieve the data. table_name : Replace this with the actual name of the table from which you want to select all data.

4. let's create a table named employees with a few columns ( employee_id , first_name , last_name , job_title , and salary). CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), job_title VARCHAR(100), salary DECIMAL(10, 2) ); INSERT INTO employees ( employee_id , first_name , last_name , job_title , salary) VALUES (1, 'John', 'Doe', 'Software Engineer', 75000.00), (2, 'Jane', 'Smith', 'Project Manager', 90000.00), (3, 'Michael', 'Johnson', 'Data Analyst', 60000.00); SELECT ALL DATA MYSQL> SELECT * FROM employees;

5. Selecting particular rows To select particular rows from a table in SQL, you can use the SELECT statement along with the WHERE clause . 1. Select all pets with gender(sex)as male (“m”) SELECT * FROM pet WHERE sex = 'm‘; This will return all rows from the pet table where the sex column is 'm'.

6. Select all male cats SELECT * FROM pet WHERE sex = 'm' AND species = 'cat'; Selecting particular columns Display names and birth_date of all pets SELECT name,birth FROM pet; 2.Display owner of pets after dec 2018 SELECT owner FROM pet WHERE birth >’2018-12-31’;

7. Eliminating Redundant Data(keyword Distinct) The distinct keyword eliminates duplicate rows from the results of a SELECT statement Display names of all pet-owners SELECT DISTINCT owner FROM pet; 2.Display distinct species of pets from table pet SELECT DISTINCT species from pet; Selecting from all the rows-ALL KEYWORD SELECT ALL city FROM suppliers;

8. 1. Selecting from all the rows-ALL KEYWORD mysql > SELECT ALL city FROM suppliers; Viewing structure of a table If you want the structure of the table,you can use Describe or Desc command. syntax DESCRIBE | DESC<table name>; Example DESC pet;

9. Sql select as aliases The AS keyword is used to give columns or tables a temporary name that can be used to identify that column or table later. Example SELECT date,type AS “Event type” FROM Event; Example SELECT first_name AS name FROM customer;

10. BETWEEN BETWEEN OPERATOR SELECTS VALUES WITHIN A GIVEN RANGE SYNTAX SELECT column_name (s) FROM table_name WHERE column_name BETWEEN value 1 AND value 2; Example SELECT * FROM PRODUCTS WHERE PRICE BETWEEN 10 AND 20;

11. CONDITION BASED ON A LIST TO specify a list of values IN operator is used, The IN operator selects value that match any value in a given list of values. IF the table contain customer_id , first_name , last_name , country Query is Mysql > SELECT first_name,country FROM customers WHERE country IN(‘USA’); IF YOU ARE USING NOT IN THEN THAT DO NOT MATCH IN THE LIST.

12. Conditions based on Pattern Matches Percent(%) the % character matches any substring Underscore(_) the _character matches any character. ‘san%’ matches any string beginning with ‘San’ % idge % matches any string containing ‘ idge ’ as substring for example ,’ Ridge’,Bridges etc ‘----’ matches any string string exactly 4 characters. ‘---%’ matches any string of at least 3 characters.

13. To list numbers which are in areas with pin codes starting with 13, the command is: SELECT firstname,lastname FROM members WHERE pin LIKE ‘13%’;

14. To list names of pets who have name ending with ‘ y’,the command would be SELECT name FROM emp WHERE name LIKE ‘%y’;

15. Write query to display the names of pets having exactly four letter names. U se pet table SELECT * FROM pet WHERE name LIKE “____”;

16. Explanation of the Query The SQL query SELECT * FROM PET WHERE NAME LIKE "____"; is used to select all rows from the table PET where the NAME column has exactly four characters. The underscore (_) in the LIKE clause is a wildcard character that matches exactly one character. By using four underscores, the query ensures that only names with exactly four characters are matched.

17. SEARCHING FOR NULL The null value in a column can be searched in a table using IS NULL in the WHERE clause.(relational operator like =,<> etc.cant be used in the with NULL)

18. SELECT empno , empname , JOB FROM emp WHERE DEPTNO IS NULL;

19. ORDER BY CLAUSE The ORDER BY clause in SQL is used to sort the result set of a query by one or more columns. By default, the sorting is done in ascending order, but you can specify descending order if needed.

20. SELECT * FROM employee ORDER BY ename ;

21. Explanation of the Output The rows are ordered alphabetically by the ename column in ascending order. This means that the rows are listed starting with "Alice", followed by "Bob", "Charlie", "David", and "Eve".

22. STRING FUNCTIONS CHAR() Returns the character for each integer passed. The SQL query :- SELECT CHAR(70,65,67,69); will return the string "FACE". Each number inside the CHAR function represents an ASCII code, and CHAR converts these codes into their corresponding characters. So, CHAR(70) is 'F', CHAR(65) is 'A', CHAR(67) is 'C', and CHAR(69) is 'E'. Therefore, the query outputs:

23. CONCAT() Returns concatenated string SELECT CONCAT( name,aggregate ) AS “NAME MARKS” FROM student WHERE age=14 or age=16;

24. SUBSTR(),SUBSTR() Returns the substring as specified. SELECT SUBSTR(‘ABCDEFG’, 3,4 ) “Subs”; OUTPUT:- CDEF

25. SUBSTR('ABCDEFG', 3, 4): This function takes three arguments: The first argument 'ABCDEFG' is the source string from which you want to extract a substring. The second argument 3 specifies the starting position within the source string ('ABCDEFG'). In SQL, string positions start at 1, so 3 indicates the third character, which is 'C'. The third argument 4 specifies the length of the substring to extract. Therefore, starting from position 3, it extracts 4 characters, resulting in 'CDEF'.

26. HOW Many characters are there in string CANDIDE? SELECT LENGTH(‘CANDIDE’) “Length in characters”; Output: 7

27. NUMERIC FUNCTIONS MOD() Returns the remainder of one expression by diving by another expression. EXAMPLE SELECT MOD(11,4) “Modulus”; Output: 3

28. POWER()/POW() Returns the value of one expression raised to the power of another expression. SELECT POWER(3,2) “Raised”; Output:- 9

29. Round() SELECT ROUND(15.200,2) “Round”; 15.20

30. Aggregate Functions 1. AVG This function computes the average of given data Syntax:- AVG([DISTINCT | ALL]n) Returns average value of parameter(s) n

31. Calculate average salary of all employees listed in table empl SELECT AVG( sal ) “Average” FROM empl ;

32. SELECT: This Keyword is used to specify the columns to be retrieved . AVG( sal ) : This function calculates the average value of the sal column. "Average" : This is an alias for the result of the AVG( sal ) function, so the output column will be labeled as "Average". FROM empl : This specifies that the data is being retrieved from the empl table .

33. COUNT This function counts the number of rows in a given column or expression. Syntax: COUNT({*[DISTINCT | ALL ]expr}) Returns the number of rows in the query. If you specify argument expr, this function return rows where expr is not null. You can count either all rows, or only distinct values of expr. If you specify the asterisk(*), this function returns all rows, including duplicates and nulls.

34. Count number of records in table empl . SELECT COUNT(*) “Total” FROM empl ;

35. MAX This function returns the maximum value from a given column or expression. MAX([ DISTINCT | ALL ] expr) Returns maximum value of argument expr

36. Display maximum salary from table empl SELECT MAX( sal ) “Maximum Salary” FROM empl ;

37. Explanation of the Output The column Maximum Salary shows the highest salary value from the sal column in the empl table. The value 8000 is the maximum salary among all employees in the table. In this example, the highest salary is 8000, which is the value in the sal column for the employee named Bob. The MAX function scans through all the values in the sal column and returns the highest one, labeled as "Maximum Salary".

38. MIN This function returns the minimum value from a given column or expression Syntax:- MIN([ DISTINCT | ALL ] expr) Returns minimum value of expr

39. Display the joining date of seniormost employee SELECT MIN( hiredate ) “Minimum HireDate ” FROM empl ;

40. SUM This function returns the sum of values in given column or expression Syntax SUM([DISTINCT | ALL] n)

41. Display total salary of all employees listed in table empl SELECT SUM( sal ) “Total Salary” FROM empl ;