SQL

SQL

SELECT TIMESTAMPDIFF(HOUR, MIN(CASE WHEN inandout = ‘in’ THEN dateandtime END), MAX(CASE WHEN inandout = ‘exit’ THEN dateandtime END))-1 AS HoursWorked, employeeid, employeename,inandout,department,  DATE(dateandtime) FROM attendance WHERE employeeid = 575 AND dateandtime BETWEEN ‘2013-08-01’ AND ‘2013-08-23’ GROUP BY employeeid, DATE(dateandtime)   Create table with below code CREATE TABLE `attendance` ( `attid` double DEFAULT NULL, `employeeid` double DEFAULT NULL, `employeename` varchar(300) DEFAULT NULL, `dateandtime` datetime DEFAULT NULL, `doorsname` varchar(300) DEFAULT NULL, `inandout` varchar(300) DEFAULT NULL, `department` varchar(300) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

What is view? A view is, in essence, a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables. . A view is actually a composition of a table in the form of a predefined SQL query. A view can contain all rows of a table
Read More

SQL Create Database Basic syntax of CREATE DATABASE statement: CREATE DATABASE DatabaseName; Example: If you want to create new database <Payroll>, then CREATE DATABASE statement would be as follows: CREATE DATABASE Payroll; Your database has been created as a name of “Payroll”

What is Table? Tables are used to provide reference to comparative data when individual comparisons are not possible or preferable. In database terms, a table is responsible for storing data in the database. Database tables consist of rows and columns. Each column contains a different type of attribute and each row corresponds to a single
Read More

what is database? A database is a collection of data. Databases are designed to offer an organized mechanism for storing, managing and retrieving information. They do so through the use of tables. General Objectives: The elimination of data redundancy is only one of many reasons for establishing a database. Others include: Integrate existing data files
Read More

What is SQL syntax? The SQL syntax is quite an easy one to grasp. Most of the actions you need to perform are done with a SQL statement. SQL is followed by unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL
Read More

What is RDBMS? RDBMS stands for Relational Database Management System. RDBMS data is structured in database tables, fields and records. Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields. RDBMS store the data into collection of tables, which might be related by common fields
Read More

What is SQL? SQL pronounced “ess-que-el”(stands for Structured Query Language) or SQL is pronounced as “S-Q-L” or “see-quill”. SQL is an ANSI and ISO standard, and is the de facto standard database query language. A variety of established database products support SQL. SQL is used to communicate with a database. SQL defines many keywords, which
Read More