Listado de la etiqueta: Statements


SQLite has a case statement which allows us to include conditional logic in our SQL statements. SQLite case expressions execute a list of conditions and return an expression based on the results. SQLite case statements behave identically to IF – THEN – ELSE statements like the other scripting languages. If the phrase is valid, we can use the SQLite case statement inside any of the clauses or SQL statements. In genérico, the SQLite Case statement starts with an optional expression and one or more WHEN… THEN clauses, an optional ELSE clause, and a necessary END keyword.

Syntax of the CASE statement in SQLite:

In SQLite, the case statement usually has one of two forms.

SQLite simple Case statement:

CASE test_statement
WHEN [condition1] THEN [statement1]
WHEN [condition2] THEN [statement2]
……
……
……
WHEN [condition(n)] THEN [statement(n)]
ELSE [statement]
END

We use the syntax above to establish many conditions to reach the desired outcome. When and then clauses are used in the above syntax. They function in a logical order. To return the final output, this method compares each statement to the list of statements. This is a fairly easy approach for executing the case statement according to the user’s requirements. Each condition and statement are interdependent, meaning, when the first condition is true, the statement is only executed after that. This way, all conditions, and statements are executed. If a condition is false, control is transferred to the else part as seen in the above syntax.

To choose the outcome, the query case statement evaluates a list of statements. It’s worth noting that the simple case expression just looks for equity. Whereas, the looked-through case statement can use any form of inspection.

SQLite Search case statement:

CASE test_statement
WHEN [BOOLEAN statement1] THEN [statement1]
WHEN[BOOLEAN statement2] THEN[statement2]
 ELSE [ statement] END

If the Boolean statement in the specified grouping is valid, the Search case statement evaluates it and returns the corresponding result. When no valid statement is found, the query case statement returns the statement in the ELSE condition. If the ELSE clause is neglected, the looked-through case statement returns NULL. When the stated criteria are met, the search case statement terminates the assessment and execution.

Creating table for CASE statement in SQLite:

First, we have created a table and gave the name “Student” with the CREATE query. The table is set with the different columns such as ID with the data type integer and create ID as a primary key, NAME, and EMAIL is set with the data type TEXT. The last column CGPA is assigned a TEXT data type. The table Student and its attributes are shown in the SQLite shell as follows:

CREATE TABLE Student(
   …>       ID INT PRIMARY KEY    ,
   …>       NAME           TEXT   ,
   …>       EMAIL          TEXT   ,
   …>       CGPA           FLOAT
   …>     );

Now, we have to insert the values against each column of the table Student. With the SQLite INSERT query, we have inserted five rows in each of the columns specified in the table Student. The screenshot below is showing the way of inserting records into the table columns.

sqlite> INSERT INTO Student VALUES (2, ‘Ibrahim’,[email protected], 3.20 );
sqlite> INSERT INTO Student VALUES (3, ‘Maha’,[email protected], 3.9);
sqlite> INSERT INTO Student VALUES (4, ‘Jennifer’, [email protected], 2.5);
sqlite> INSERT INTO Student VALUES (5, ‘Rehan’, [email protected], 3.10 );

Let’s view whether the data is recorded in the table Student. By using the SELECT statement, we have retrieved the entire data in the table Student in a table format. The aforementioned statement is represented in the screenshot below:

sqlite> SELECT * FROM Student;

Example 1: Program of using SQLite simple CASE statement:

We have used a simple CASE statement with the SELECT statement to show the workings of the CASE statement in SQLite.

Here, we have used a SELECT statement and selected a column ID, NAME, and CGPA from the table Student. After that, we used a CASE statement that compares the CGPA of the Student. It matches the CGPA with the condition whether the CGPA is greater than or equal to 4.00 or not. If so, then it gives that CGPA an A+. But if the condition is false, then CASE will switch to the next statement and check whether the CGPA is greater than 3.5. If the condition is true, then it assign this CGPA a grade “A”.

Like this, the control is passed to each case statement until the CGPA does not meet the given condition. If all the cases are false, then the else clause will be executed and will print a statement FAIL.

sqlite> SELECT ID, NAME, CGPA,
   …> CASE
   …> WHEN CGPA >= 4.00 THEN «A+»
   …> WHEN CGPA >= 3.5 THEN «A»
   …> WHEN CGPA >= 3.0 THEN «B»
   …> WHEN CGPA >= 2.5 THEN «C»
   …> ELSE «FAIL»
   …> END AS «GRADE»
   …> FROM Student;

Now, we will perform the query and look at the outcome. It should look like this: The GRADE column is included in the table Student along with the values from the CASE statement.

Example 2: Program of using SQLite search CASE statement:

To determine the outcome, the searched CASE statement analyses a set of expressions. The simple CASE expression simply compares for equality. Whereas, the searching CASE expression can compare in any way.

A Boolean case statement is used in the example given. For this, we have a SELECT query through which we have selected three attributes ID, NAME, and CGPA from the table Student. Only these attributes and their corresponding values will appear as the output. Then, we have a CASE keyword for the SQLite CASE statement. The condition is applied with the WHEN keyword. First, case statement checks if the CGPA is greater than 3.5 and then assigns the grade A. If the condition is not satisfied here, then we will move to our second case statement which checks the condition whether is satisfied here or not. If both our cases are false, then the else part will execute and print the grade C.

sqlite> SELECT ID, NAME, CGPA,
   …> CASE
   …> WHEN CGPA > 3.5 THEN «A»
   …> WHEN CGPA >  3.0 AND CGPA < 2.5 THEN «B»
   …> ELSE
   …> «C»
   …> END «GRADE»
   …> FROM Student;

When executing the above CASE query, the results are obtained like this:

Conclusion:

We studied the basic syntax of case statements in this article. We also saw a variety of case statement instances. The rules for case statements were also taught. We learned using the SQLite case statement in this post and when to do so.



Source link


“SQLite allows us to run commands straight from a file. This is particularly beneficial if you have a lengthy program, such as establishing several different tables and populating them with data. While using the SQLite command prompt shell, use the .mode dot command in association with the .output or .merienda commands to output your query results to a file, or use the .read command to read the SQL command from the file in the SQLite shell. Here, we will see how to execute the SQL command in the SQLite shell and show the results in the file.

We must first install the SQLite3 shell application before we can use it. If SQLite is installed, then check that the program’s location has been added to our particular PATH environment variable since this will make it easier to access the database file as well as other files we might need. Then, cd to the database file’s folder from a command prompt. Make sure the file is in the same directory folder as the SQLite database you generated.”

Use sqlite3 as a command; this should bring up the SQLite shell, with a screen similar to the one seen below. First, we have specified the path of the folder where our file is then created a database name “hospital.db” by using the sqlite3 command.

The database is successfully created in SQLite; you can see it by using a .databases command in the shell. Now, we have a CREATE statement which is used to create a table in the given database. We created a table with the name “patients.” This table keeps the record of the patients, which includes ID as the primary key with the data type INT, NAME with the CHAR data type, and WARD with the type TEXT.

CREATE TABLE patients(
      ID             INT     ,
      NAME           CHAR(10)   ,
      WARD          FLAOT    
    );

The table is being built, and the columns are being defined. Using the INSERT command, we have now included the data for each column.

INSERT INTO patients VALUES (1, ‘Nayab’, ‘medical’);
INSERT INTO patients VALUES (2, ‘Sadia’,‘cardiology’ );
INSERT INTO patients VALUES (3, ‘Aman’,‘neurosurgery’);
INSERT INTO patients VALUES (4, ‘Balaj’,‘skin specialist’);
INSERT INTO patients VALUES (5, ‘Raima’,‘urology’);

Example 1
We are running SQL commands here, and the results are printed in the file. The following commands must be used to accomplish this: The .header on the command is used to turn on the result set’s heading.

To direct the sqlite3 tool to provide the result in CSV mode, specify the output mode to CSV.

The .output FILENAME command is used to save the outcome of a query to a file. Following the .output command, all subsequent queries’ results will be stored in the file you selected in the FILENAME parameter. The .merienda FILENAME command is used if you just wish to save the outcome of another single query to a file.

We can use the .output command without any arguments to re-display the query’s result to the standard output.

.headers ON
.mode csv
.output File.txt

After using the above command, we have a SELECT statement that displays the table and the table record within our file.

The SQL command runs here when we open our file, and the table record is displayed.

Example 2
As in the above example, we have shown how to insert the record from the shell to the file. Here, we have a SQL select command in the file. This command fetches all the records in the table patients. We have used the method .read file name.

The File.txt script reads from the current folder/directory in this case. If in a separate directory, specify the entire path. By using the .read File.txt in the SQLite shell, the table is viewable here as we have executed the command from the file.

SELECT NAME, WARD
FROM patients
ORDER BY NAME
LIMIT 4;

Example 3
Here also, we are executing the SQL command from the file but with the .timer command in the shell. You can use the .timer command to activate or deactivate CPU time measurements. SQLite will maestro and report the operating system duration needed to process each query if this option is enabled. Firstly, we have the UPDATE command, which sets the NAME = UZAMA with the WHERE CLAUSE, which has the ID = 1 in the File.txt.

UPDATE patients SET NAME = ‘Uzama’ WHERE ID = 1

Now, we have set the header on command and also the timer on command. Then, use the .read File.txt to display the results of the command from the File.txt. Notice that the timer is on and showing the record with the updated values as follows:

.header ON
.mode COLUMN
.timer ON
.read File.txt

Example 4
Here’s a different approach to using the .read command. The following SQL command from the File.txt, which we are executing in the SQLite shell.

Without having to open SQLite, you can use the .read command. The contrast between this case and the last one is that the last one was done after you’d already connected to SQLite from within SQLite. This example, however, is executed from outside of SQLite. We have given the command “sqlite3 hospital.db “.read File.txt” within the folder where the database and file are present. The results are represented in the image below.

sqlite3 hospital.db «.read File.txt»

Example 5
This is our third method for executing SQL commands from the file. The command in File.txt is as follows:

SELECT *FROM patients
WHERE ID < 3
ORDER BY NAME ASC;

When connecting to SQLite and opening a database with sqlite3, the following approach forwards the information to the database file.

sqlite3 hospital.db< File.txt

Example 6
Another way to execute SQL commands from the file is by using the init method. Below is the SQL command, which will be executed by the init method.

SELECT WARD FROM patients;

Hence, the command “sqlite3 hospital.db -init File.txt” also executes the SQL command from the file as shown in the snapshot.

sqlite3 hospital.db init File.txt

Conclusion

In the end, we have shown the four different ways which execute SQL commands from the file. First, we have to place the file and the database on the same path; then, we can execute the several SQL commands successfully. Each method is implemented by displaying the output.



Source link


In programming as well as scripting languages including PowerShell, experiencing decision-making scenarios or dealing with different conditions are very common. PowerShell deals with such situations using conditional statements/expressions such as if, else-if, etc. These decision-making statements are responsible to manage the program’s flow based on different conditions. Most of the time, “If” and “else” statements are used collectively so that the script must run in every case.

This write-up will present a thorough guide for the if-else statements:

  • What is if-statement in PowerShell?
  • Syntax of if-statement.
  • What is if-else statement in PowerShell?
  • Syntax of if-else statement.

So let’s get started!

What is if-statement in PowerShell?

The if statement in PowerShell takes an expression/condition in its parenthesis and tests it. Consequently, it will return either a true or false value, if the specified condition is true then the code-block associated with the if-statement will get executed. The if-statement deals with the true condition only, it has nothing to do with the false condition.

Syntax of if-statement

The below-given snippet shows the basic syntax of if-statement in PowerShell:

if(expression/condition) {
   // Executes only if the given expression is true
}

Let’s consider the below script to understand the working of if-statement in PowerShell:

$a =12;
$b =15;
if($a -le $b) {
  write-host(«a is less than or equal to b»);
}

In this example program, we utilized the if-statement to test an expression, if the returned value is true then the body of if-statement will execute else not:

The output verified the working of the if-statement.

What if the returned value of the specified expression is false? How if-statement will deal with the false value?

$a =12;
$b =15;
if($a -ge $b) {
  write-host(«a is less than or equal to b»);
}

The above script will generate the following output:

The cursor moved to the next line without performing any specific task. It verified that the if-statement doesn’t handle the false conditions.

What is if-else statement in PowerShell?

To tackle the false conditions, the else statement can be used along with the if-statement. In Powershell, if we utilized the combination of if and else statements, as a result, both true and false conditions will be tackled.

Syntax of if-else statement
The below snippet depicts the basic syntax of the if-else statement in PowerShell:

if(test-condition/expression) {
   // Executes only if the given expression is true
}
else{
    // Executes if the specified expression is not true
}

How to use if-else statement in PowerShell
Below snippet will assist you in this regard:

$a =20;
$b =15;
if($a -le $b) {
  write-host(«a is less than or equal to b»);
}
else{
  write-host(«a is greater than b»);
}

This time we utilized both if and else statements, now if the value of a is less than or equal to the b then body of if-statement will execute otherwise the body of else-statement will execute:

The above snippet verified that the else-statement was executed because the specified condition was false.

Conclusion

In PowerShell, decision-making statements such as if, else, and else-if are used to manage the program’s flow based on different conditions. The if-statement deals with the true condition only while the else-statement deals with the false condition only. Therefore, in PowerShell, if and else statements can be used combinedly to handle both true and false conditions. This write-up explained all the basics of if and else conditions in PowerShell using some suitable examples.



Source link