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

MySQL practical Tutorials part 9- SQL not operator, SQL Not Like, SQL greater than, SQL less than greater than operator

 ========================================================================= Not Equal SELECT title FROM books WHERE released_year = 2017;   SELECT title FROM books WHERE released_year != 2017;   SELECT title, author_lname FROM books;   SELECT title, author_lname FROM books WHERE author_lname = 'Harris';   SELECT title, author_lname FROM books WHERE author_lname != 'Harris'; ========================================================================= Not Like SELECT title FROM books WHERE title LIKE 'W';   SELECT title FROM books WHERE title LIKE 'W%';   SELECT title FROM books WHERE title LIKE '%W%';   SELECT title FROM books WHERE title LIKE 'W%';   SELECT title FROM books WHERE title NOT LIKE 'W%'; ========================================================================= Greater Than SELECT title, released_year FROM books ORDER BY released_year;   SELECT title, released_year FROM books  WHERE released_year > 2000 ORDER BY release...
Post a Comment
Read more

MULTIPLEXER , Design & Implement the given 4 variable function using IC74LS153. Verify its Truth-Table

TITLE: MULTIPLEXER   AIM: Design & Implement the given 4 variable function using IC74LS153. Verify its Truth-Table.   LEARNING OBJECTIVE: ·        To learn about IC 74153 and its internal structure. ·        To realize 8:1 MUX and 16:1 MUX using IC 74153.   COMPONENTS REQUIRED: IC 74153, IC 7404, IC 7432, CDS, wires, Power supply. IC PINOUT:            1)     IC 74153 2)      IC 7404:                                              3) IC 7432 THEORY:   ·        Multiplexer is a combinational circuit that is one of the most widely used in digital design. ·        The multiplexer is a data selector which gates one out of several inputs to a sin...
Post a Comment
Read more