MySQL Functions and Queries Guide
Uploaded by
mansipanda.29MySQL Functions and Queries Guide
Uploaded by
mansipanda.29Common questions
Powered by AIYou can extract the substring 'net' from 'Internet is a boon' using the SQL SUBSTRING() function. The query would be: SELECT SUBSTRING('Internet is a boon', 4, 3); This command starts at the 4th character of 'Internet is a boon' and retrieves 3 characters thereafter, extracting the substring 'net' .
The system's current date can be retrieved using the CURDATE() function in SQL. To display the day of the week, you can use the DAYNAME() function, which returns the name of the day for a given date .
The typical categories of single-row functions in SQL include Numeric, String, and Date functions. Numeric functions perform arithmetic operations (e.g., ROUND, TRUNCATE), string functions manipulate text (e.g., CONCAT, LENGTH), and date functions process temporal data (e.g., DAYOFMONTH, MONTH). Each category serves different purposes: numeric functions handle numbers, string functions work with text, and date functions manipulate date formats, making SQL a versatile language for data processing .
The INSTR function in SQL is used to find the position of a substring within a string. It returns the first occurrence of the substring. The SQL statement would be structured as: SELECT INSTR('MainString', 'SubString'); For example, SELECT INSTR('Enjoying MySQL', 'My'); would return the starting position of 'My' in the string 'Enjoying MySQL' .
To round contributions to the nearest rupee, use the ROUND function: SELECT ROUND(Contribution, 0) FROM Charity;. To truncate, use TRUNCATE: SELECT TRUNCATE(Contribution, 0) FROM Charity;. Rounding adjusts the number to the nearest whole number based on decimal value (rounding up if 0.5 or above), while truncation removes the decimal part without adjustment, thus always rounding down .
Numeric functions operate on numeric data types and return numeric values, performing mathematical calculations such as ROUND, TRUNCATE, or POW. In contrast, string functions manipulate string data to return strings or numeric values representing string information, such as LENGTH or INSTR. This differentiation lies in the type of data they operate on and the nature of the result .
To display the current month's name, you can use the MONTHNAME() function. For the date 10 years from now, you can use the DATE_ADD() function. The query would look like: SELECT MONTHNAME(CURDATE()) AS CurrentMonth, DATE_ADD(CURDATE(), INTERVAL 10 YEAR) AS Future; This query provides the current month and computes the date 10 years from today, presenting it under the 'Future' column .
To display all first names in lowercase from a table named 'Charity', you can use the SQL LOWER() function. The SQL query would be: SELECT LOWER(FirstName) FROM Charity; This query selects the FirstName field from the 'Charity' table and converts each first name to lowercase before displaying it .
To retrieve items purchased on specific days like Monday or Tuesday, use the DAYNAME function in conjunction with a WHERE clause. For example, the query SELECT ItemName FROM Grocer WHERE DAYNAME(Date_Purchase) IN ('Monday', 'Tuesday'); fetches item names with purchases on either Monday or Tuesday by comparing the day's name extracted from 'Date_Purchase' .
To display the length of addresses along with the Person ID, you can use the SELECT statement with the LENGTH function: SELECT P_Id, LENGTH(Address) FROM Charity;. This query retrieves the Person ID and the length of each corresponding address in the 'Charity' table .