Skip to main content

MySQL practical Tutorials part 4- Using Distinct, order by(asc, desc) clause, limit and wildcard character (like keyword)

 =======================================================================================================================

Examples od distinct keyword in mysql. Please refer previous articles for create database/table commands. Then you can refer this for further queries. Kindly follow step by step articles.

CODE: Using DISTINCT

SELECT author_lname FROM books;

 

SELECT DISTINCT author_lname FROM books;

 

SELECT author_fname, author_lname FROM books;

 

SELECT DISTINCT CONCAT(author_fname,' ', author_lname) FROM books;

 

SELECT DISTINCT author_fname, author_lname FROM books;


=======================================================================================================================

CODE: Sorting Data with ORDER BY

SELECT author_lname FROM books;

 

SELECT author_lname FROM books ORDER BY author_lname;

 

SELECT title FROM books;

 

SELECT title FROM books ORDER BY title;

SELECT author_lname FROM books ORDER BY author_lname DESC;

 

SELECT released_year FROM books;

 

SELECT released_year FROM books ORDER BY released_year;

 

SELECT released_year FROM books ORDER BY released_year DESC;

 

SELECT released_year FROM books ORDER BY released_year ASC;

 

SELECT title, released_year, pages FROM books ORDER BY released_year;

 

SELECT title, pages FROM books ORDER BY released_year;

 

SELECT title, author_fname, author_lname 

FROM books ORDER BY 2;

 

SELECT title, author_fname, author_lname 

FROM books ORDER BY 3;

 

SELECT title, author_fname, author_lname 

FROM books ORDER BY 1;

 

SELECT title, author_fname, author_lname 

FROM books ORDER BY 1 DESC;

 

SELECT author_lname, title

FROM books ORDER BY 2;

 

SELECT author_fname, author_lname FROM books 

ORDER BY author_lname, author_fname;

=======================================================================================================================

CODE: Using LIMIT

SELECT title FROM books LIMIT 3;

 

SELECT title FROM books LIMIT 1;

 

SELECT title FROM books LIMIT 10;

 

SELECT * FROM books LIMIT 1;

 

SELECT title, released_year FROM books 

ORDER BY released_year DESC LIMIT 5;

 

SELECT title, released_year FROM books 

ORDER BY released_year DESC LIMIT 1;

 

SELECT title, released_year FROM books 

ORDER BY released_year DESC LIMIT 14;

 

SELECT title, released_year FROM books 

ORDER BY released_year DESC LIMIT 0,5;

 

SELECT title, released_year FROM books 

ORDER BY released_year DESC LIMIT 0,3;

 

SELECT title, released_year FROM books 

ORDER BY released_year DESC LIMIT 1,3;

 

SELECT title, released_year FROM books 

ORDER BY released_year DESC LIMIT 10,1;

 

SELECT * FROM tbl LIMIT 95,18446744073709551615;

 

SELECT title FROM books LIMIT 5;

 

SELECT title FROM books LIMIT 5, 123219476457;

 

SELECT title FROM books LIMIT 5, 50;


=======================================================================================================================

CODE: Better Searches with LIKE

SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%';

 

SELECT title, author_fname FROM books WHERE author_fname LIKE 'da%';

 

SELECT title FROM books WHERE  title LIKE 'the';

 

SELECT title FROM books WHERE  title LIKE '%the';

 

SELECT title FROM books WHERE title LIKE '%the%';


=======================================================================================================================

CODE: LIKE Part 2: More Wildcards


 

SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '____';

 

SELECT title, stock_quantity FROM books WHERE stock_quantity LIKE '__';

 


SELECT title FROM books WHERE title LIKE '%\%%'

 

SELECT title FROM books WHERE title LIKE '%\_%'


=======================================================================================================================


CODE: Refining Selections Exercises Solution

SELECT title FROM books WHERE title LIKE '%stories%';

 

SELECT title, pages FROM books ORDER BY pages DESC LIMIT 1;

 

SELECT 

    CONCAT(title, ' - ', released_year) AS summary 

FROM books ORDER BY released_year DESC LIMIT 3;

 

SELECT title, author_lname FROM books WHERE author_lname LIKE '% %';

 

SELECT title, released_year, stock_quantity 

FROM books ORDER BY stock_quantity LIMIT 3;

 

SELECT title, author_lname 

FROM books ORDER BY author_lname, title;

 

SELECT title, author_lname 

FROM books ORDER BY 2,1;

 

SELECT

    CONCAT(

        'MY FAVORITE AUTHOR IS ',

        UPPER(author_fname),

        ' ',

        UPPER(author_lname),

        '!'

    ) AS yell

FROM books ORDER BY author_lname;


=======================================================================================================================


Labels:
Location: Ahmednagar, Maharashtra, India

Comments

Post a Comment

Popular posts from this blog

Add, remove, search an item in listview in C#

Below is the C# code which will help you to add, remove and search operations on listview control in C#. Below is the design view of the project: Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; namespace Treeview_control_demo {     public partial class Form2 : Form     {         public Form2()         {             InitializeComponent();             listView1.View = View.Details;                   }         private void button1_Click(object sender, EventArgs e)         {             if (textBox1.Text.Trim().Length == 0)...
Post a Comment
Read more

display files and directories in Listview

Below is the C# code which displays all files and directories in listview control with their file size and creation date. If it is file then it also displays the extension of the file e.g. .txt, .jpg etc Below is the design view of the project: Listview to display files and directories with size and date created Below is the source code of the project: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.IO; namespace search_in_listview {     public partial class Form1 : Form     {         public Form1()         {             InitializeComponent();                   }         private void button1_Click(object sender, EventArgs ...
Post a Comment
Read more

Add worklog in Jira using Python

 Below is the Python code to add the worklog in Jira. You need to install a request library for this. Here is the code: import requests from requests.auth import HTTPBasicAuth import json url = "https://your jira address here/rest/api/2/issue/ticket_number/worklog" auth = HTTPBasicAuth("username", "jira access token") headers = {     "Accept": "application/json",     "Content-Type": "application/json" } payload = json.dumps({     "comment": {         "content": [             {                 "content": [                     {                         "text": "This is for QA Testing",                         "type": "text"                     } ...
Post a Comment
Read more