Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Alias

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

       Aliases

      The MySQL Alias

      The MySQL Alias is used to assign a temporary name, called an Alias to a table or a column in SQL.
      Aliases are created using the AS keyword and are used to refer to a specific table or a column without changing its original name. They are used to make the query easily readable when working tables or columns with similar names.

      Aliasing Column Names

      Aliasing column names is used to assign a different name to a column of a table.

      Syntax

      The basic syntax of a column alias is as follows 
      SELECT column_name 
      AS alias_name
      FROM table_name;

      Example

      First, let us create a table with the name CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS (
         ID INT NOT NULL,
         NAME VARCHAR (20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Now, let us insert values into the table created above using the INSERT INTO statement as shown below 
      INSERT INTO CUSTOMERS VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      The CUSTOMERS table obtained is as follows
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00

      Example

      In the following query, we are creating two aliases, one for the ID column and one for the AGE column 
      SELECT ID AS CUST_ID, AGE 
      AS CUST_AGE
      FROM CUSTOMERS;

      Output

      The output of the above query is produced as given below
      CUST_ID
      CUST_AGE
      1
      32
      2
      25
      3
      23
      4
      25
      5
      27
      6
      22
      7
      24

      Example

      If we want the alias name to contain spaces, we can use the double quotation marks as shown in the query below 
      SELECT ID AS "CUST ID", AGE 
      AS "CUST AGE"
      FROM CUSTOMERS;

      Output

      On executing the given query, the output is displayed as follows
      CUST ID
      CUST AGE
      1
      32
      2
      25
      3
      23
      4
      25
      5
      27
      6
      22
      7
      24

      Example

      In the query below, we are creating an alias named 'INFORMATION' that combines two columns (AGE, ADDRESS) 
      SELECT ID, CONCAT(AGE, ', ', ADDRESS) 
      AS INFORMATION
      FROM CUSTOMERS;

      Output

      When we execute the above query, the output is obtained as follows
      ID
      INFORMATION
      1
      32, Ahmedabad
      2
      25, Delhi
      3
      23, Kota
      4
      25, Mumbai
      5
      27, Bhopal
      6
      22, Hyderabad
      7
      24, Indore

      Aliasing Table Names

      Aliasing table names is used to assign a different name to a table.

      Syntax

      Following is the syntax of a table alias 
      SELECT column1, column2....
      FROM table_name AS alias_name

      Example

      Let us create another table with the name ORDERS using the following query 
      CREATE TABLE ORDERS (
         OID INT NOT NULL,
         DATES DATETIME NOT NULL,
         CUSTOMER_ID INT NOT NULL,
         AMOUNT INT NOT NULL,
         PRIMARY KEY (OID)
       );
      Now, let us insert values into the table created above using the INSERT INTO statement as follows 
      INSERT INTO ORDERS VALUES 
      (102, '2009-10-08 00:00:00', 3, 3000),
      (100, '2009-10-08 00:00:00', 3, 1500),
      (101, '2009-11-20 00:00:00', 2, 1560),
      (103, '2008-05-20 00:00:00', 4, 2060);
      The ORDERS table obtained is as follows
      OID
      DATES
      CUSTOMER_ID
      AMOUNT
      100
      2009-10-08 00:00:00
      3
      1500
      101
      2009-11-20 00:00:00
      2
      1560
      102
      2009-10-08 00:00:00
      3
      3000
      103
      2008-05-20 00:00:00
      4
      2060
      In the following query, the CUSTOMERS table is aliased as 'C' and the ORDERS table is aliased as 'O' 
      SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
      FROM CUSTOMERS AS C, ORDERS AS O
      WHERE  C.ID = O.CUSTOMER_ID;

      Output

      This would produce the following result
      ID
      NAME
      AGE
      AMOUNT
      3
      Kaushik
      23
      1500
      2
      Khilan
      25
      1560
      3
      Kaushik
      23
      3000
      4
      Chaitali
      25
      2060

      Aliasing with Self Join

      The MySQL Self Join is used to join a table to itself as if it were two separate tables. Aliasing in self join is used to temporarily rename the table in the SQL statement to prevent confusion.

      Syntax

      Following is the syntax for performing a self-join with aliases 
      SELECT column_name(s)
      FROM my_table a, my_table b
      ON a.join_column = b.join_column

      Example

      Now, let us join the CUSTOMERS table to itself using the self join to establish a relationship among the customers on the basis of their earnings.
      Here, we are aliasing column names and table names to create a more meaningful resultant table.
      SELECT a.ID, b.NAME 
      AS EARNS_HIGHER, a.NAME 
      AS EARNS_LESS, a.SALARY 
      AS LOWER_SALARY
      FROM CUSTOMERS a, CUSTOMERS b
      WHERE a.SALARY < b.SALARY;

      Output

      Output of the above query is as follows
      ID
      EARNS_HIGHER
      EARNS_LESS
      LOWER_SALARY
      2
      Ramesh
      Khilan
      1500.00
      2
      Kaushik
      Khilan
      1500.00
      6
      Chaitali
      Komal
      4500.00
      3
      Chaitali
      Kaushik
      2000.00
      2
      Chaitali
      Khilan
      1500.00
      1
      Chaitali
      Ramesh
      2000.00
      6
      Hardik
      Komal
      4500.00
      4
      Hardik
      Chaitali
      6500.00
      3
      Hardik
      Kaushik
      2000.00
      2
      Hardik
      Khilan
      1500.00
      1
      Hardik
      Ramesh
      2000.00
      3
      Komal
      Kaushik
      2000.00
      2
      Komal
      Khilan
      1500.00
      1
      Komal
      Ramesh
      2000.00
      6
      Muffy
      Komal
      4500.00
      5
      Muffy
      Hardik
      8500.00
      4
      Muffy
      Chaitali
      6500.00
      3
      Muffy
      Kaushik
      2000.00
      2
      Muffy
      Khilan
      1500.00
      1
      Muffy
      Ramesh
      2000.00